MySQL: Using GROUP BY vs subselect in columns list

Question

In my application I have two MySQL tables, 'units' and 'impressions' in relation one to many. I need to fetch list of all ad units from units table but also fetch impressions count for each ad unit.

I have two SELECT queries to do this task (simplified for this example), first using sub-select:

SELECT 
    (SELECT COUNT(*) FROM impressions WHERE impression_unit_id = unit_id) AS impressions_count,
    unit_id
FROM units;

and second using GROUP BY:

SELECT
    COUNT(impression_id) AS impressions_count,
    unit_id
FROM units
LEFT JOIN impressions ON impression_unit_id = unit_id
GROUP BY unit_id;

Sub-select query runs for each record (ad unit) so GROUP BY looks smarter but it has one JOIN more. Which one to prefer for performance?

Answer 1

The GROUP BY query will perform better. The query optimizer might optimize the first query to use a join, but I wouldn't count on it since it is written to use a dependent sub-query, which will be much slower. As long as the tables are properly indexed, JOINs should not be a major concern for performance.

The first query, if it doesn't get optimized to use a JOIN will have to run the sub-query for each row in the unit table, where the JOIN query does it all in one operation.

To find out how the query gets optimized, run an EXPLAIN of both queries. If the first one uses a dependent sub-query, it will be slower.