鍍金池/ 問答/數(shù)據(jù)庫/ SQL查詢COUNT慢。

SQL查詢COUNT慢。

查詢一個句子的COUNT(1), JOIN IN 了 4張表。總記錄數(shù)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

這是執(zhí)行計劃：
圖片描述
在SQL上面我是比較差，沒有啥辦法解決問題。但是想從sql上解決，也學一些這方面的知識。
如果硬要我自己解決我可能就會新增三個字段，把三個需要查找的name在保存的時候存入數(shù)據(jù)庫，（不涉及 group 的表）就只用查一張表了。
謝謝能幫我解答。

回答
編輯回答
我不懂

如果僅是為了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
2018年1月23日 16:15