Query resorting to filesort?

MySQL & MariaDB Other MySQL® Questions
1

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!

2

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.

3

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.

4

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.

5
[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.
6

If you use innodb as the table type, index on active is enough. Innodb primary key is always in the secondary indexes.

7
[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.