Left Join of a Subselect query very slow

Question

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:

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.

Answer 1

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

Answer 2

Using DISTINCT on each aggregation function helped to avoid duplicates items (on group_concats) and allowed me to used the query as @akina suggested.

Final query

SELECT
    -- Opportunity Id
    nlni.id as id,

    -- Opportunity target countries ids
    group_concat(DISTINCT opp_country.id SEPARATOR '|') as countries_id,
    -- Opportunity target countries
    ifnull(group_concat(DISTINCT opp_country.name ORDER BY opp_country.name SEPARATOR '|'), 'Worldwide') as countries,
    -- Opportunity target regions ids
    group_concat(DISTINCT opp_region.id SEPARATOR '|') as regions_id,
    -- Opportunity target regions
    ifnull(group_concat(DISTINCT opp_region.name ORDER BY opp_region.name SEPARATOR '|'), 'Worldwide') as regions,

    -- Opportunity target macro industries
    group_concat(DISTINCT opp_macro_industry.name SEPARATOR '|') as macro_industry

FROM
    newsletter_newsitem as nlni
    -- N:N relational table for areas
    LEFT JOIN newsletter_newsitem_areas as nlni_areas
       ON nlni_areas.newsitem_id = nlni.id
   -- List of countries
   LEFT  JOIN newsletter_geoarea as opp_country
       ON opp_country.id = nlni_areas.geoarea_id
           AND opp_country.type = 2
   -- List of regions
   LEFT  JOIN newsletter_geoarea as opp_region
       ON opp_region.id = nlni_areas.geoarea_id
           AND opp_region.type = 1
   -- N:N relational table for industries
   LEFT JOIN newsletter_newsitem_industries as nlni_industries
       ON nlni_industries.newsitem_id = nlni.id
  -- List of macro industries
  LEFT JOIN newsletter_industry as opp_macro_industry
      ON opp_macro_industry.id = nlni_industries.industry_id
           AND opp_macro_industry.granularity = 1
WHERE
    -- Exclude invalid/archived opps
    nlni.archive_id IS NULL
    AND nlni.id IS NOT NULL
GROUP BY
    nlni.id

Still, I'm not sure if DISTINCT hurts performance significantly or if it's a scalable solution (I want to add more aggregations)

Answer 3

• Is the LEFT JOIN backwards?
• Check whether some columns are NULLable but should be NOT NULL.
• Follow my tips on many-many mapping tables
• Consider avoiding the many-many by using Lists and tags
• Learn about "composite" indexes.