How can I optimize this simple database and query using php and mysql

Questions

I pull a range (e.g. limit 72, 24) of games from a database according to which have been voted most popular. I have a separate table for tracking game data, and one for tracking individual votes for a game (rating from 1 to 5, one vote per user per game). A game is considered "most popular" or "more popular" when that game has the highest average rating of all the rating votes for said game. Games with less than 5 votes are not considered. Here is what the tables look like (two tables, "games" and "votes"):

games:
gameid(key)
gamename
thumburl

votes:
userid(key)
gameid(key)
rating

Now, I understand that there is something called an "index" which can speed up my queries by essentially pre-querying my tables and constructing a separate table of indices (I don t really know.. that s just my impression).

I ve also read that mysql operates fastest when multiple queries can be condensed into one longer query (containing joins and nested select statements, I presume).

However, I am currently NOT using an index, and I am making multiple queries to get my final result.

What changes should be made to my database (if any -- including constructing index tables, etc.)? And what should my query look like?

Thank you.

Answers

Your query that calculates the average for every game could look like:

SELECT gamename, AVG(rating)
FROM games INNER JOIN votes ON games.gameid = votes.gameid
GROUP BY games.gameid
HAVING COUNT(*)>=5 
ORDER BY avg(rating) DESC
LIMIT 0,25

You must have an index on gameid on both games and votes. (if you have defined gameid as a primary key on table games that is ok)

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/8465057/how-can-i-optimize-this-simple-database-and-query-using-php-and-mysql

Related