MySQL Performance Tuning JOIN + GROUP BY
Example
A common problem that leads to an inefficient query goes something like this:
SELECT ...
FROM a
JOIN b ON ...
WHERE ...
GROUP BY a.id
First, the JOIN expands the number of rows; then the GROUP BY whittles it back down the the number of rows in a.
There may not be any good choices to solve this explode-implode problem. One possible option is to turn the JOIN into a correlated subquery in the SELECT. This also eliminates the GROUP BY.
