ORDER BY null kills MySQL filesorts dead

I spent some time today optimizing OtherInbox.  As our private beta expands, we are starting to see heavier usage, and so it's time to revisit some of my beloved SQL queries.

I use the MySQL slow query log to find out which queries were taking the most time -- that's been a wonderful tool that I plan to blog about later.  I will note that the output is much easier to make sense of if you parse it first with this script.  (It's really old, so I had to modify it to look at Query_time instead of Time)

Using the EXPLAIN command for each of the slow queries identified in the above log, I found one that was especially disturbing.   According to EXPLAIN's "extra" column, MySQL was resolving these queries with two fairly expensive operations: 

Using where; Using temporary; Using filesort

Fixing the "Using temporary" required some nimble manipulation of indices, but filesort was really perplexing me.  I wasn't using an ORDER BY clause, so as I read the docs, there was no reason to be doing a filesort.  But then I remembered that MySQL automatically does ordering based on GROUP BY clauses.  All I had to do was add "ORDER BY null" to the end of my query, and that did the trick:

Using where; Using temporary

If only all optimizations were so simple.