mysql - 左联接的Group_Concat变慢。我也需要左表中的所有行

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

2个表的结果太慢。

最佳答案

这是您的查询：

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

您应该从索引开始。我建议applicant(type, id)和note(applicant_id)。并且在其中一个中包含delete_type（我不知道它来自哪个表）。

其次，使用相关子查询可以使此查询更快。看起来像：

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

delete_type上的条件要么在外部查询中要么在子查询中-尚不清楚它来自哪个表。

这样可以避免外部数据上很大的group by，这可以提高性能。