Get rid of "using filesort", optimize the select

[syff]

I am trying to optimize my MySQL queries, as the server has been very slow for months now.

I have been running EXPLAINs on many queries this morning, and most of them seem to be very optimized. However, I just came across one that appeared to run very slow:

SELECT id, nick, score, sex, (YEAR(CURDATE())-YEAR(fodt)) - (RIGHT(CURDATE(),5)<RIGHT(dob,5)) AS age FROM db_users WHERE votes > 29 AND pic = 2 ORDER BY score DESC LIMIT 5

As you may see, this tries to select the 5 users with the best score.

The EXPLAIN on this query returns the following:

id  select_type  table  type  possible_keys  key  key_len  ref  rows  Extra  
1 SIMPLE db_users ref votes,pic_votes pic_votes 1 const 4577 Using where; Using filesort

I reckon this is an index problem. How would I go on indexing the columns to optimize my query?

[FalseDawn]

Do you want to know _how_ to add indexes to columns, or what would be a suitable index?

If the latter, I'd suggest trying either
a) One index covering the 3 columns votes,pic, score
b) One index covering votes,pic and a second index on score

Indexes are tricky things - sometimes you just need to experiment.
Also, the distribution of data in your tables can influence just how effective cerain indexes are.

[edited by: FalseDawn at 4:08 pm (utc) on Sep. 1, 2006]