0

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.

3
  • Duplicates aren't related to the subselect usage.
    – Kondybas
    Jul 13, 2018 at 12:22
  • 1
    The issue is the subselect itself because the dbms cannot use indexes on "outer" query but always do table scan. Try materializing the inner select into a table, inserting som indexes than doing the join.
    – DDS
    Jul 13, 2018 at 12:24
  • Perhaps you have the LEFT JOIN backwards??
    – Rick James
    Jul 13, 2018 at 18:49

3 Answers 3

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
1
  • As I explained in the last paragraph that won't work because I have to append others aggregations to the definitive query and such aggregations will produce duplicity. The query in the sample will get the countries but I'll also need a column with the list of continents. To make that I'll have to append another left join to the query proposed (noticed that type = 1 instead of 2) LEFT JOIN newsletter_geoarea as opp_region ON opp_region.id = nlni_areas.geoarea_id AND opp_region.type = 1 This will cause that list of continents will get repeated as many countries an news item has
    – Layo
    Jul 13, 2018 at 15:06
0

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)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.