I'm using mysql 5.5. and I have three tables:
newsletter_newsitem
: contains a row per news itemnewsletter_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.
LEFT JOIN
backwards??