SQL query COUNT is slow.

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.

Mar.04,2021

if it's just for count , I think your sql is equivalent to the one below

.
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
MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b31563-2bd6b.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b31563-2bd6b.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?