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.