Optimize a rankings page using PHP and MySQL

Question

I could really use some help optimizing a table on my website that is used to display rankings. I have been reading a lot on how to optimize queries and how to properly use indexes but even after implementing changes I thought would work, little improvement can be seen. My quick fix has been simply to use only the top 100,000 rankings (updated daily and stored in a different table) to improve the speed for now, but I really don't like that option.

So I have a table that stores the information for users that looks something like:

table 'cache':

id    (Primary key)
name
region
country
score

There are other variables being stored about the user, but I don't think they are relevant here as they are not used in the rankings.

There are 3 basic ranking pages that a user can view:

A world view:

SELECT cache name,region,country,score FROM cache ORDER BY score DESC LIMIT 0,26

A region view:

SELECT name,region,country,score FROM cache WHERE region='Europe' ORDER BY score DESC LIMIT 0,26

and a country view:

SELECT name,region,country,score FROM cache WHERE region='Europe' AND country='Germany' ORDER BY score DESC LIMIT 0,26

I have tried almost every combination of indexes I can think of to help alleviate work for the database, and while some seem to help a little bit I can't find one that will only return 26 rows for both the region and country queries(with simply an index on 'score' the world rankings are blazing fast).

I feel like I might be missing something basic, any help would be much appreciated!

Little extra info: the cache table is currently around 920 megabytes with a little more than 800,000 rows total. If you could use any more info just let me know.

Answer 1

put ONE index on country, score, region. Too many indices will slow you down.

Answer 2

Your world rankings benefit from the score index because score is the only criteria in the query. The logical sequence it sorts on is built into the query. So that's good.

The other queries will benefit from an index on region. However, similar to what @Matt indicates, a composite index on region, country and score may be the best bet. Note, the three columns for the key should be in region, country, score sequence.

Answer 3

How many records are we talking about?

I am no SQL guru, but in this case, if just changes to indexes did not do the trick. I would consider playing around with the table structure to see what perf gains I could get.

Cache

id (pk)
LocationId (index)
name
score

Location

LocationId (pk)
CountryId (index) maybe
RegionId (index) maybe

Country

CountryId
name

Region

RegionId
name

Location

LocationId (Primary key) CountryId
RegionId

Country

CountryId name

Region

RegionId name

Temp tables in Procs would allow you to select on Location Id in every case. It would reduce the over all complexity of the issue you are having: you would be troubleshooting 1 query plan, not 3.

The effort would be high, and the payoff would be until you were done, so I would suggest looking at the index approach first.

Good luck

Answer 4

Explain is your friend. Ony phone or I would post a link. Google "mysql explain"