Especially when you are using sub-selects (AKA subqueries) things gets not that clear when analyzing the results of EXPLAIN. A good technique is to run your subqueries and try to tune them separately before trying to adjust the whole query.
I found myself wearing the DBA hat during this long weekend resulting in a speed improvement of more than four thousand times.
As MySQL manual suggests make sure you run EXPLAIN and look first at those entries with the "extra" field stating "Using temporary" and/or "Using filesort".
Then identify the query bits using those conflicting tables and try to isolate them (divide)
Continue running EXPLAIN again on those bits to come up with a solution that increase performance (conquer).
Contrary to the belief it is not always a lack of indexes, in fact too many indexes will slow the query or other queries down, for sure it will slow down inserts.
Many times is about a query that should be rewritten.
Look for things like not filtering in the subquery. The WHERE clause is your friend. In my case I found the developer used a WHERE clause in the wrapper dataset but not in the sub-selects where he could have done the same for the very same key.
Look for multiple keys or indexes that are just redundant. Remember the rule: A composite index/key is read from left to right, there is no need to add other keys or indexes with portions of the composite, if your fields are organized in that order you can use one, two or more fields and that unique composite key will be enough.
Any job can be done using multiple ways, a developer can use any but the engineer should always pick the faster. Let us strive to be better Engineers when we develop code! Rewrite your query for which of course you must have documentation of what you are trying to achieve. Yes documentation is on the right side in the Agile Manifesto but that actually does not mean it is not necessary.
Learn by challenge. You will not master MySQL optimization by reading the whole manual and ten books. You will master it as you face problems and wonder why the occur. In that process you will definitely learn *just* what you need to perform your job.
In extreme cases some table structures will have to be changed. Even de-normalization will be sometimes the only option but please be sure there is nothing else to do with indexes or rewriting your query. Changing table structure might be hiding a lot more time, complexity and probably no that much efficiency improvement.