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.

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.

What type of storage engine is for the messages table? Does indexing on the 'active' column help at all?

Also you may use the timestamp to resolve the problem. Put a index on the timestamp column and in your query put "timestamp > [some time]" where sometime is help enough for you to get the last 30 active messages.

I suggest you to rethink about application logic to see what the query really supports. Most time you can rewrite query in a more efficient form.

I had something similar happen. See if this might help:
http://forum.percona.com/index.php/m/5281/?srch=slow#msg_528 1

Troy

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.

Quote:

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.

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

gmouse wrote on Sat, 01 May 2010 00:42

Quote: 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.

jerry wrote on Sat, 01 May 2010 01:16

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

Wrong, if not explicitly in the index definition, the index is not sorted on PK.