Left Join of a Subselect query very slow
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