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]
[B]fmpwizard wrote on Wed, 31 October 2007 04:17[/B]
you could try having individual keys for the type,value,score columns.
I think that as score is the last part of your composite key, it cannot be used.
Actually jorje29 has done everything right.
A query like that where all columns in the WHERE part is AND’ed together and then sorted by the last column will return the result without any filesort.
And individual indexes will not be faster than a combined index on a query like this.
But what throws MySQL of in this case is the “value >=89.2 AND value <= 129.2”.
Try it with an absolute value like “value = 90” for example and you can see that it gives you the execution plan you are after.
As for a solution for this lack of optimization that MySQL doesn’t handle I’m not really sure how to solve it.
The only thing I can give you as advice directly is that you increase the sort_buffer_size so that the sorting is performed in RAM (unless you have already done so).
BTW newer versions of MySQL is using the modified filesort algorithm as default to avoid reading the data twice read more about it here.
[B]myshpa wrote on Thu, 01 November 2007 19:59[/B]
Hi,
any tips for avoiding filesort in a simple query like this?
EXPLAIN SELECT id, name
FROM table_name
WHERE id IN ( 222839, 299872, 301535 )
ORDER BY FIELD( id, 222839, 299872, 301535 )
It’s mysql 4.1, innodb, according to EXPLAIN primary (id) index is used, but it still uses filesort.
The thing is that on a query like this you don't have to care about the filesort.
Sorting is one of the last bits of processing that is performed before the server returns the result.
And in a query like this you have already limited the result to only 3 records with the where clause.
Which means that MySQL needs to “filesort” 3 rows and that takes about a nanosecond.
The query in the beginning of this thread is a totally different matter since that query’s WHERE probably returned a lot of rows and then you only want the top 20 of them since it uses ORDER BY … DESC together with LIMIT 20.
That is why he wants to avoid a filesort.
So filesort on thousands of rows is bad, while filesort on just a few rows doesn’t matter.