Group_Concat with left join is slowing down. I need all rows in left table as well

Question

SELECT applicant.id, applicant.firstname, applicant.lastname, applicant.state, applicant.mobile, applicant.lead_description, GROUP_CONCAT(note.note SEPARATOR ', ') as notes
FROM applicant LEFT JOIN
     note
     ON applicant.id = note.applicant_id
WHERE delete_type = 0 AND applicant.type = 0
GROUP BY applicant.id
ORDER BY applicant.id DESC

The result is too slow from 2 tables.

Answer 1

This is your query:

SELECT a.id, a.firstname, a.lastname, a.state, a.mobile, a.lead_description,
       GROUP_CONCAT(n.note SEPARATOR ', ') as notes
FROM applicant a LEFT JOIN
     note n
     ON a.id = n.applicant_id
WHERE delete_type = 0 AND a.type = 0
GROUP BY a.id
ORDER BY a.id DESC

You should start with indexes. I would recommend applicant(type, id) and note(applicant_id). And including delete_type in one of them (I don't know which table it comes from).

Second, this query may be faster using a correlated subquery. This would look like:

SELECT a.id, a.firstname, a.lastname, a.state, a.mobile, a.lead_description,
       (select GROUP_CONCAT(n.note SEPARATOR ', ')
        from note n
        where a.id = n.applicant_id
       ) as notes
FROM applicant a
WHERE delete_type = 0 AND a.type = 0
ORDER BY a.id DESC

The condition on delete_type either goes in the outer query or the subquery -- it is not clear which table this comes from.

This avoids the large group by on the external data, which can be a performance boost.