# Query resorting to filesort? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [April 27, 2010, 7:22pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390 "2010-04-27T19:22:00Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![edstart](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@edstart](https://forums.percona.com/u/edstart)\ **Post date:** [April 27, 2010, 7:22pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/1 "2010-04-27T19:22:00Z")
Hi, I am puzzled by the behavior of mysql lately, hoping someone can help. We have a web app (a polling chat application) doing about 500 queries/sec and everything has been fine for the past few months. Until recently, we’ve been fortunate to run into few problems. We have recently noticed a routine select query end up in our slow logs and an EXPLAIN indicates that mysql is doing a filesort. The select looks something like: SELECT user\_id, body, timestamp FROM messages WHERE active = 1 ORDER BY id DESC LIMIT 30 Normally this query would run and mysql would use the correct index to sort. However it now seems to resort filesort and causes huge lock ups resulting in a cascading problem slowing down the rest of the application. Can anyone shed some light on this behavior? Many thanks! ---
**Author:** ![jerry](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jerry](https://forums.percona.com/u/jerry)\ **Post date:** [April 27, 2010, 7:42pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/2 "2010-04-27T19:42:24Z")
How large is your message table now? You can do a select timestamp, count(\*) from messages group by 1 to find out the growth rate. It may be simply the table is too big now. You can also add active = 1 to see more about your data distributions. ---
**Author:** ![edstart](https://avatars.discourse-cdn.com/v4/letter/e/8dc957/32.png) [@edstart](https://forums.percona.com/u/edstart)\ **Post date:** [April 27, 2010, 7:46pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/3 "2010-04-27T19:46:55Z")
The data set is relatively small; less than a million rows and under 100mb. Could it be sort\_buffer\_size? Our current my.cnf is set to about 2mbs. ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [April 28, 2010, 5:29pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/4 "2010-04-28T17:29:04Z")
Without seeing your create table statement for your table (so that we also see which indexes you have) it’s hard to answer but I’m going to give it a go. Your sort\_buffer\_size would probably not affect it so much since what you want MySQL to do is to use an index to solve the ORDER BY since I’m guessing that there are quite a lot of rows that have the status “active”. The ideal index is a compound one with either (active, id) or (id, active) where the combination of the two columns will solve both the WHERE condition and the ORDER BY at the same time. You will have to test which is the fastest in your case. With either of those two indexes MySQL should be able to quickly find the 30 correct records and then quit the processing of the query without any filesort. If that still doesn’t solve the situation then use a hint to tell MySQL which index that you suggests it to take when executing this particular query. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [April 30, 2010, 5:12pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/5 "2010-04-30T17:12:49Z")
| [B]Quote:[/B] | | The ideal index is a compound one with either (active, id) or (id, active) where the combination of the two columns will solve both the WHERE condition and the ORDER BY at the same time. | Only the former will work, please read more into multi column indices. ---
**Author:** ![jerry](https://avatars.discourse-cdn.com/v4/letter/j/5daacb/32.png) [@jerry](https://forums.percona.com/u/jerry)\ **Post date:** [April 30, 2010, 5:46pm UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/6 "2010-04-30T17:46:41Z")
If you use innodb as the table type, index on active is enough. Innodb primary key is always in the secondary indexes. ---
**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\ **Post date:** [May 1, 2010, 5:44am UTC](https://forums.percona.com/t/query-resorting-to-filesort/1390/7 "2010-05-01T05:44:54Z")
| [B]gmouse wrote on Sat, 01 May 2010 00:42[/B] | | | [B]Quote:[/B] | | The ideal index is a compound one with either (active, id) or (id, active) where the combination of the two columns will solve both the WHERE condition and the ORDER BY at the same time. | Only the former will work, please read more into multi column indices. | Correct, sorry sometimes you write a bit too fast. ;) The second index is for queries that uses IN() or BETWEEN combined with ORDER BY to avoid a filesort. Not when you have a const value in the WHERE clause like in this case.