MySQL Performance: MySQL => Avoid "using filesort"...

Avoid "using filesort"... [message #2139]

Tue, 30 October 2007 08:50

jorje29
Messages: 7
Registered: October 2007
Location: Greece

Junior Member

I have this query :

SELECT id, playerid1, playerid2, playerid3, score, value, scoretowinthemax, maxchange from table WHERE type = 123 and value >= 89.2 and value <= 129.2 and score > 12 order by score desc limit 20;

I have an index type_value_score(type,value,score) but when I run the query and do explain, I get that it uses "using filesort" ( although it uses my index ) and it becomes slow because it needs to retrieve the rows twice for sorting the results. Usually if I have an index like the one above, it works... Any ideas how to overcome "using filesort" ? ( My table has 4,2 millions rows )

here is my explain output :

[MYSQL]+--------+-------+-------------------------------------------------------------------------------------+------------------+---------+------+--------+----------------------------+
| table  | type  | possible_keys                                                                       | key              | key_len | ref  | rows   | Extra                      |
+--------+-------+-------------------------------------------------------------------------------------+------------------+---------+------+--------+----------------------------+
| ztrade | range | type_value_score,type_value_scoretowinthemax,type_score,type_scoretowinthemax,score | type_value_score |      14 | NULL | 348625 | where used; Using filesort |
+--------+-------+-------------------------------------------------------------------------------------+------------------+---------+------+--------+----------------------------+
1 row in set (0.00 sec)[/MYSQL]

Report message to a moderator