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.