Mysql – Left Join of a Subselect query very slow

MySQLmysql-5.5

I'm using mysql 5.5. and I have three tables:

  • newsletter_newsitem: contains a row per news item
  • newsletter_newsitem_areas: a many-to-many table that relates a news item to a region (a news item can have many regions)
  • newsletter_geoarea: contains a row per region.

I want to get all the countries a news item have so I crafted the following query which take less than a second to execute:

SELECT nlni_areas.newsitem_id,
       ifnull(GROUP_CONCAT(nlga.name ORDER BY nlga.name
                         SEPARATOR '|'), 'Worldwide') as name
FROM  newsletter_geoarea as nlga
    INNER JOIN newsletter_newsitem_areas as nlni_areas
        ON nlni_areas.geoarea_id = nlga.id
WHERE nlga.type = 2
GROUP BY nlni_areas.newsitem_id

This query returns a news item id and a concatenated list of countries. (one row per news item). However, I want to LEFT JOIN the result of the query to the newsletter_newsitem table to extract more data from each news item so I crafted the following query:

SELECT
    nlni.id as id,    
    opp_country.name as countries
FROM
    newsletter_newsitem as nlni
    LEFT JOIN (
        SELECT nlni_areas.newsitem_id,
               ifnull(GROUP_CONCAT(nlga.name ORDER BY nlga.name
                              SEPARATOR '|'), 'Worldwide') as name
        FROM  newsletter_geoarea as nlga
            INNER JOIN newsletter_newsitem_areas as nlni_areas
                ON nlni_areas.geoarea_id = nlga.id
        WHERE nlga.type = 2
        GROUP BY nlni_areas.newsitem_id
    ) as opp_country 
        ON opp_country.newsitem_id = nlni.id
WHERE
    nlni.archive_id IS NULL
    AND nlni.id IS NOT NULL

This query turns out to be extremely slow (take more than 5 min). I'm joining by columns that are indexed (nlni_areas.geoarea_id nlga.id, nlni.id).

I can't explain why joining the news item table with the subselect table takes that long given that both tables have only a single row per news item.

The EXPLAIN statement:

Explain big query

I could've joined the tables without using a subselect but since I need to perform other aggregations (similar to this one) I can't afford to have more than one row per news item or I'll have duplicates.

Best Answer

I'm not sure in my accuracy... test

SELECT nlni.id as id,    
       COALESCE(GROUP_CONCAT(nlga.name ORDER BY nlga.name SEPARATOR '|'),
                'Worldwide') as countries
FROM newsletter_newsitem as nlni
LEFT JOIN newsletter_newsitem_areas as nlni_areas ON nlni_areas.newsitem_id = nlni.id
LEFT JOIN newsletter_geoarea as nlga ON nlni_areas.geoarea_id = nlga.id AND nlga.type = 2
GROUP BY nlni.id
WHERE nlni.archive_id IS NULL
  AND nlni.id IS NOT NULL
Related Question