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-inform...
2) http://www.mysqlperformanceblog.com/2006/07/24/extended-expl...
3) http://www.mysqlperformanceblog.com/2010/06/15/explain-exten...