2 good queries are better than good speech to understand which column choose to group when you want to get the count of a join.
The first query takes 12 seconds:
SELECT n.*, COUNT(DISTINCT(l.like_id)) AS nb_likes FROM news n JOIN like l ON n.news_id = l.news_id GROUP BY l.news_id
The second one take only 0.5 seconds:
SELECT n.*, COUNT(DISTINCT(l.like_id)) AS nb_likes FROM news n JOIN like l ON n.news_id = l.news_id GROUP BY n.news_id
These two queries produce exactly the same result: all the news and the number of likes for each news.
FYI, the news table has 300 records and the like table has 5M records.
The moral is this: when you need to perform this kind of operation, always make the group by on the key (PK or FK with an index of course) of the lower table in terms of recordsets.
Comment