Can someone recommend a good tutorial on MySQL indexes, specifically when used in an order by clause during a join?

Question

I could try to post and explain the exact query I'm trying to run, but I'm going by the old adage of, "give a man a fish and he'll eat for a day, teach a man to fish and he'll eat for the rest of his life." SQL optimization seems to be very query-specific, and even if you could solve this one particular query for me, I'm going to have to write many more queries in the future, and I'd like to be educated on how indexes work in general.

Still, here's a quick description of my current problem. I have a query that joins three tables and runs in 0.2 seconds flat. Awesome. I add an "order by" clause and it runs in 4 minutes and 30 seconds. Sucky. I denormalize one table so there is one fewer join, add indexes everywhere, and now the query runs in... 20 minutes. What the hell? Finally, I don't use a join at all, but rather a subquery with "where id in (...) order by" and now it runs in 1.5 seconds. Pretty decent. What in God's name is going on? I feel like if I actually understood what indexes were doing I could write some really good SQL.

Anybody know some good tutorials? Thanks!

Answer 1

Not a very riveting answer, but I've found the official docs to give good background:

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

A few things you could try (assuming you've already analysed the optimizer path with EXPLAIN):

• Try running the query with USE INDEX (<your index name here>) to see if your index usage would actually provide the performance you expect.
• Make sure the order of your columns in any composite index reflects the way you expect that index to be used (sorry if that's a bit vague, but MySql index usage can be a bit quirky at times).

Answer 2

I would recommend two books I recently read:

1. High Performance MySQL
2. Refactoring SQL Applications