SQL查询COUNT慢。

Question

查询一个句子的COUNT(1), JOIN IN 了 4张表。总记录数3800,耗时43秒
这是SQL：

select count(1) from (
SELECT a.id AS "id", a.batch_id AS "batchId", a.product_type AS "productType", a.retrospect_type AS "retrospectType", a.operator_name AS "operatorName", a.add_time AS "addTime", a.medicine_name AS "medicineName", a.check_result AS "checkResult", a.create_date AS "createDate", a.is_open AS "isOpen", a.remark AS "remark", 
regionale.name AS "regionaleName", 
stald.name AS "staldName", 
sorter.name AS "sorterName", 
(select GROUP_CONCAT(b.url) from retrospect_attachment b where b.bid=a.id and b.type='1' group by b.bid) as 'pictures', 
(select GROUP_CONCAT(c.url) from retrospect_attachment c where c.bid=a.id and c.type='2' group by c.bid) as 'vedios' 
FROM retrospect_details a 
JOIN tilbage_stald_sorter tss ON tss.id = a.stald_sorter_id 
JOIN tilbage_stald stald ON stald.id = tss.stald_id 
JOIN tilbage_regionale regionale ON regionale.id = stald.regionale_id 
JOIN tilbage_sorter sorter ON sorter.id = tss.sorter_id WHERE a.del_flag = 0
) tmp_count

这是执行计划：

在SQL上面我是比较差，没有啥办法解决问题。但是想从sql上解决，也学一些这方面的知识。
如果硬要我自己解决我可能就会新增三个字段，把三个需要查找的name在保存的时候存入数据库，（不涉及 group 的表）就只用查一张表了。
谢谢能帮我解答。

Answer 1

如果仅是为了count一下，我想你的sql 和下面的是等价的

SELECT count(a.id)
FROM retrospect_details a 
JOIN tilbage_stald_sorter tss ON tss.id = a.stald_sorter_id 
JOIN tilbage_stald stald ON stald.id = tss.stald_id 
JOIN tilbage_regionale regionale ON regionale.id = stald.regionale_id 
JOIN tilbage_sorter sorter ON sorter.id = tss.sorter_id WHERE a.del_flag = 0