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外部数据过大，从而可以提高性能。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句