Home » 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] |
|
|
| Re: Avoid "using filesort"... [message #2140 is a reply to message #2139 ] |
Tue, 30 October 2007 23:17  |
fmpwizard
Messages: 6
Registered: September 2007
Location: USA |
Junior Member |
|
|
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.
Diego Medina
Web Developer
http://www.fmpwizard.com
|
|
| |
| Re: Avoid "using filesort"... [message #2143 is a reply to message #2142 ] |
Wed, 31 October 2007 19:53 |
jorje29
Messages: 7
Registered: October 2007
Location: Greece |
Junior Member |
|
|
fmpwizard and sterin thank you for your replies...I solved the problem with a unique way, but not perfect...I want also to ask you if you know anything further about this example below :
As I wrote
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;
with index (type,value,score)
using filesort
BUT
( Order is asc now, not desc )
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 asc limit 20;
doesn't !!!!
my index is (type,value,score)
I tried to create index (type,value,score desc) but it didn't work and also I didn't know how to verify that the index is in desc order...
I decided to put "score" field into my table, in reverse order so I can sort in ascending order, using the index, avoiding filesort and output the "score" field after mutliplying it with(-1)...
The surprise is here : (type,value,score) didnt work !!! but (type,score) did !!!
So, I changed
WHERE type = 123 and value >= 89.2 and value <= 129.2 and score > 12
to
WHERE type = 123 and score > 12 and value >= 89.2 and value <= 129.2
I have my solution but still I don't know how to sort indexes according to my needs ( order by column )...Do you know how ?
thanx again
[Updated on: Wed, 31 October 2007 20:06]
|
|
| | | |
| Re: Avoid "using filesort"... [message #2153 is a reply to message #2139 ] |
Thu, 01 November 2007 14:59 |
myshpa
Messages: 2
Registered: November 2007 |
Junior Member |
|
|
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.
Suggestions anyone?
|
|
| |
| Re: Avoid "using filesort"... [message #2155 is a reply to message #2154 ] |
Thu, 01 November 2007 17:20 |
myshpa
Messages: 2
Registered: November 2007 |
Junior Member |
|
|
yes, that's it, it works ... maybe a little bit slower (i've just verified it works, no performance tests yet), but it really seems ok
thank you,
m.
|
|
|
| Re: Avoid "using filesort"... [message #2156 is a reply to message #2153 ] |
Thu, 01 November 2007 18:41 |
sterin
Messages: 324
Registered: March 2007
Location: Sweden |
Senior Member |
|
|
| myshpa wrote on Thu, 01 November 2007 19:59 |
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.
|
|
| |
| Re: Avoid "using filesort"... [message #2158 is a reply to message #2157 ] |
Thu, 01 November 2007 19:26 |
|
| jorje29 wrote on Thu, 01 November 2007 19:07 |
Sterin, do you know, in my example, why index (type,value,score) didn't work but (type,score) did ??? I really don't have an idea...
|
This is how indexes actually work. You have strict condition for "type" field, which allows mysql to choose some subset of the index rows, then it uses second part od the index to retrieve rows with "score > 12" and check them against "value >= 89.2 and value <= 129.2" condition. That is why I suggested to create an index on these three columns - to let mysql perform all WHERE checks and then only retrieve rows by primary key.
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
| |
| Re: Avoid "using filesort"... [message #2160 is a reply to message #2159 ] |
Thu, 01 November 2007 20:08 |
|
| jorje29 wrote on Thu, 01 November 2007 19:44 |
Scoundrel,
My question is :
Now I use (type,score,value) index and it doesn't use "filesort"
but
with (type,value,score) it uses "filesort"
Why ?
I changed my query to :
WHERE type = 123 and score > 12 and value >= 89.2 and value <= 129.2
|
For all following text T=type, V=value, s=score.
When you use (T,V,S) index on your table, you can look at it as on a copy of your original table, ordered by T, then by V, then by S. Now Try to put yourself on mysql's place: when you need to execute mentioned query you will do the following:
1) find a subset of rows where T=123 - it is easy because your data is ordered by T
2) get all records where T=123 and use only those, which have V between 89.2 and 129.2 - it is easy again because your table is ordered by V inside every distinct subset where T = const.
3) choose records where S > 12 - it is as easy as scan all records in step2 results and choose all needed rows
4) SORT resuts by S because all of them are ordererd by V (T is const).
If you'd use (T,S,V) key, then you'll:
1) find a subset of rows where T=123 - it is easy because your data is ordered by T
2) choose records where T=123 and use only those, which have S > 12 - it is easy again because your table is ordered by S inside every distinct subset where T = const.
3) take step2 results and get all records where V is between 89.2 and 129.2 - index scan
4) RETURN records to your user w/o sorting because all of your records are already ordered by S (while T is const).
Alexey Kovyrin, MySQL Performance Expert
MySQL Performance Blog
MySQL Consulting
|
|
|
Goto Forum:
Current Time: Fri Jul 10 07:04:08 EDT 2009
Total time taken to generate the page: 0.01965 seconds |
Members
Search
Help
Register
Login
Home
