Wednesday, May 21, 2008
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 filesortFixing 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.
Labels: databases, mysql, optimization, otherinbox
