Take the 2-minute tour ×

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)

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.

share|improve this question
Nice try Ponies. We know you mean well. :-) –  bobs Aug 12 '10 at 0:41
I knew something looked off. Fixed. –  Baumage Aug 12 '10 at 1:03
add comment

3 Answers

up vote 2 down vote accepted

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.

share|improve this answer
Thanks for the response but I've actually already tried using this index. It definately is 10x faster then doing a full table scan but doesn't show the speed I need when searching for countries in particular. If this is the best index to use maybe this problem has just come down to not having a powerful enough server to host this database? –  Baumage Aug 12 '10 at 16:06
From what you provided it seems that that index would be best. Are there times when the query has a country filter but no region filter? If yes, then an index on region will probably provide the best solution for that type of query. I don't agree with the statement "Too many indices will slow you down." You want to create the "right" indexe(s), and that may mean more than one. –  bobs Aug 12 '10 at 17:24
add comment

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

share|improve this answer
add comment

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.


id (pk)
LocationId (index)


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






LocationId (Primary key) CountryId


CountryId name


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

share|improve this answer
We're talking about 850,000+ records and growing by about 2,500/day. I will keep this response in mind for a time when I have time to restructure the tables. –  Baumage Aug 12 '10 at 15:56
add comment

Your Answer


