MySQL5 : MySQL Optimizations - Group by and Count

2013-07-24
Laurent Bientz

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.

Louis - 2013-12-26 08:47:37
C'est assez flippant, merci bien pour l'info, ca devrait être dans les best practices MySQL !

Comment

USER EXPERIENCE

Wandi invites you to discover a new approach to navigating a website...
Are you ready?

Launch the experience