MySQL 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.