query the COUNT of a sentence (1), JOIN IN has 4 tables. The total number of records is 3800, and it takes 43 seconds
this is 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
this is the execution plan:
I am relatively poor on SQL, and there is nothing I can do to solve the problem. But if you want to solve the problem from sql, you can also learn some knowledge about it.
if I had to solve it myself, I might add three new fields, save the three name I need to find in the database, and look up only one table (a table that does not involve group).
Thank you for helping me out.