Can someone recommend a good tutorial on MySQL indexes, in particular when it is used in an order by clause during a connection?

Question

I could try to publish and explain the exact request that I am trying to run, but I follow the old saying: "Give a man fish and he will eat during the day, teach a man fish and he will eat for the rest of his life." SQL optimization seems to be very specific to queries, and even if you could solve this one specific query for me, I would need to write many more queries in the future, and I would like to get an education on how indexes work in general.

However, here is a brief description of my current problem. I have a query that joins three tables and works in 0.2 seconds. Awesome. I add the sentence "order by" and works after 4 minutes and 30 seconds. Sucky. I am denormalizing one table, so there is another join, add indexes everywhere, and now the query runs in ... 20 minutes. What the heck? Finally, I do not use the connection at all, but rather a subquery with "where id in (...) order by", and now it works after 1.5 seconds. Pretty decent. What happens by the name of God? It seems to me that if I really understood what indexes were doing, I could write really good SQL.

Does anyone know good lessons? Thank!

Answer 1

Not a very exciting answer, but I found official docs to give a nice background:

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

A few things you could try (assuming you have already analyzed the optimizer path with EXPLAIN):

• Try using the query USE INDEX (<your index name here>)to find out if your index usage provides the expected performance.
• Make sure the column order in any composite index is as you expect the index to be used (sorry if this is a bit vague, but using the MySql index can be a little fancy sometimes).

Answer 2

I would recommend two books that I recently read:

• High Performance MySQL
• SQL