I use the cheapest and the best tool available: EXPLAIN EXTENDED You cannot do any better than have the DB tell you exactly what it is going to do with your qu

I use the cheapest and the best tool available: EXPLAIN EXTENDED You cannot do any better than have the DB tell you exactly what it is going to do with your query. Then you can experiment with changing / adding / removing clauses and see how it would affect the query plan produced by EXPLAIN. For example if EXPLAIN says the query would generate a temp table you could often achieve improvement by managing the same temp tables explicitly. Many times you can get a huge performance lift by using a "group by index". You could identify and rewrite un-indexed table scans too. I've found lots of other "unobvious" optimizations that cut down queries that ran for days or hours to minutes or seconds. Here are a few references to get started- 1) http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html http://dev.mysql.com/doc/refman/5.5/en/execution-plan-inform... 2) http://www.mysqlperformanceblog.com/2006/07/24/extended-explain/ http://www.mysqlperformanceblog.com/2006/07/24/extended-expl... 3) http://www.mysqlperformanceblog.com/2010/06/15/explain-extended-can-tell-you-all-kinds-of-interesting-things/ http://www.mysqlperformanceblog.com/2010/06/15/explain-exten...