The Lab

MySQL5 : MySQL Optimizations - Nested subquery

2013-07-24

Laurent Bientz

I think anyone who has had to analyze large tables to make reporting has already made such queries:

SELECT
    *,
    (SELECT COUNT(DISTINCT(l.like_id)) FROM like l WHERE l.news_id = n.news_id) AS nb_likes
FROM
    news n
WHERE
    blabla

Except imagine that you have 1 million news, MySQL will perform 1 million and 1 queries to retrieve all:

1 query to retrieve 1M news
1M queries to retrieve the number of likes by news

The solution to implement to optimize all is to join directly the subassembly with the likes and finally work in all 2:

SELECT
    *
    COUNT(DISTINCT(l.like_id)) AS nb_likes
FROM
(
    SELECT
        *
    FROM
        news n
    WHERE
        blabla
) AS n
JOIN like l ON n.news_id = l.news_id
GROUP BY
    l.news_id

We get the same result in only 3 queries:

1 query to retrieve 1M news
1 query to join the subset of news with all likes
1 query to group and deduce the count as well as information from your news

The difference in performance is exponential, in my case with a table of 1M recordsets, I passed from 30s to 0.02s.

Comment

Roma - 2013-09-02 07:42:13

For anyone looking for a quick example, be aware that there is no flashVarsLoaded property and there is no useFlashVars() method. I'm not sure if the intention was to show more code, but this is confusing at best. It makes it look as though the difference between AS2 and AS3 is so much deeper than it is. Instead of accessing a variable on _level0, you access a property on root.loaderInfo.parameters (AS-only or Flash projects) or Application.application.parameters (Flex projects).

Ludo - 2014-12-05 14:14:22

La bonne manière d'écrire la requête serait plutôt :
SELECT
n.*,
COUNT(DISTINCT(l.like_id)) AS nb_likes
FROM
news n
JOIN like l ON n.news_id = l.news_id
WHERE
blabla
GROUP BY
l.news_id;

COLIN - 2018-07-30 17:28:40

Stylé

THEO - 2018-07-30 17:29:13

Trop bien

MySQL5 : MySQL Optimizations - Nested subquery

Comment

USER EXPERIENCE