I've been consistently surprised by how little optimization MySQL does on joins. As of 5.6, it didn't even eliminate join clauses that weren't referenced in either the select or where clause which should be a pretty basic optimization and one whose absence hurts a lot given MySQL's nested loops join algorithm.
Haven't really used mysql in recent years (6+), but I remember once optimizing a query with about 8 inner joins, grouping and a very long where statement. After setting up all the indexes it still didn't perform as we hoped. Moving some of the conditions up into the ON parts just like in that blog post drastically improved performance. It seemed like in the naive approach, mysql really did all the joins first and only in the end started filtering. I was quite surprised that wasn't handled by the query optimizer, but maybe that has improved since then.