多表联查 group by order by 优化问题请教

SELECT
count( 0 )
FROM
(
SELECT
LIST.PROJECT_CODE,
LIST.PROJECT_NAME,
LIST.TASK_ID,
LIST.TASK_CODE,
LIST.TASK_NAME,
TASK.TASK_MANAGER_NAME,
TASK.CITY,
LIST.DESIGN_NAME,
LIST.DESIGN_SPECIFICATIONS,
LIST.MEASUREMENT_UNIT,
IF
( LIST.DELETE_FLAG = 0, LIST.DESIGN_AMOUNT, 0 ) AS DESIGN_AMOUNT,
LIST.UNIT_PRICE,
LIST.REMARKS,
LIST.APPROVAL_COMPLETE_TIME,
TASK.REPLY_DATE,
GROUP_CONCAT(
DISTINCT
IF
( COOP.PROJECT_ROLE_CODE = 'ALL_SJY' OR COOP.PROJECT_ROLE_CODE = 'ALL_SJZFZR', COOP.COOPERATION_COMPANY_NAME, NULL )) AS COOPERATION_COMPANY_NAME,
DIS.DISPATCH_UNIT_NAME,
( CASE TASKSTATUS.TASK_COMPLETE_STATUS WHEN '0' THEN '未完工' WHEN '1' THEN '已完工' ELSE NULL END ) AS TASK_COMPLETE_STATUS
FROM
MMAT_MATERIAL_LIST LIST -- 项目物资表
LEFT JOIN MMAT_TASKS TASK ON LIST.TASK_ID = TASK.TASK_ID -- 任务表
LEFT JOIN MMAT_PROJECT_COOPERATNER COOP ON LIST.TASK_ID = COOP.TASK_ID -- 项目成员表
AND COOP.DELETE_FLAG = 0
LEFT JOIN MMAT_DISPATCH_MESSAGE DIS ON LIST.TASK_ID = DIS.TASK_ID -- 派工单
AND DIS.DELETE_FLAG = 0
LEFT JOIN MMAT_TASKS_STATUS TASKSTATUS ON LIST.TASK_ID = TASKSTATUS.TASK_ID -- 任务状态
AND TASK.DELETE_FLAG = 0
WHERE
1 = 1
AND LIST.MATERIAL_SOURCE = '甲供'
AND DIS.DELIVERY_TYPE = '02'
AND LIST.TASK_ID IN (

从其他查询拿到的结果，数据量在 500 左右

)
GROUP BY
LIST.TASK_ID,
LIST.DESIGN_NAME,
LIST.DESIGN_SPECIFICATIONS,
LIST.MEASUREMENT_UNIT
ORDER BY
LIST.PROJECT_CODE,
LIST.TASK_CODE DESC
) tmp_count


有如上 sql ，查询出来数据大概需要 20 秒上下，非常慢

GROUP BY 是因为 生产环境有部分重复数据且被其他业务引用，短时间无法清理，故此使用了 GROUP BY 进行了去重

COOP 表虽然本 sql 没用到，但是部分业务会在这个表加条件

实际前台是个分页查询，这个是分页对应的 count 总数量的。

分页查询也很慢


查询结果约 5 万数据


执行计划如下图
https://s1.ax1x.com/2022/05/18/Ooa9UA.png


请问各位大佬 该如何优化