optimize this mysql query for the maximum performance and avoid redundancy
WITH result AS
        (
            (
                WITH t1 AS (
                    SELECT
                        tar.analysis_id,
                        NULL AS analysis_err_id,
                        COALESCE(trim(REGEXP_REPLACE(tdui.device_name, '[[:space:]]+', ' ')), '') AS d_device_name,
                        COALESCE(trim(REGEXP_REPLACE(tcui.device_name, '[[:space:]]+', ' ')), '') AS c_device_name,
                        tcri.recipe_no AS recipe_no,
                        COALESCE(trim(REGEXP_REPLACE(tar.measure_hash, '[[:space:]]+', ' ')), '') AS measure_hash,
                        pj.carrier_id AS carrier_id, 
                        mpp.lot_id,                    
                        COALESCE(trim(REGEXP_REPLACE(tad.wafer_no, '[[:space:]]+', ' ')), '') AS wafer_no,
                        tad.start_date as add_date,
                        COALESCE(trim(REGEXP_REPLACE(tcri.remark, '[[:space:]]+', ' ')), '') AS remark,
                        tar.result,
                        twi.defect_num,
                        COALESCE(trim(REGEXP_REPLACE(tar.pm_time, '[[:space:]]+', ' ')), '') AS pm_time,
                        COALESCE(trim(REGEXP_REPLACE(tar.recovery_time, '[[:space:]]+', ' ')), '') AS recovery_time,
                        COALESCE(trim(REGEXP_REPLACE(tad.mtbc, '[[:space:]]+', ' ')), '') AS mtbc,
                        COALESCE(trim(REGEXP_REPLACE(tad.mwbc, '[[:space:]]+', ' ')), '') AS mwbc,
                        date_format(twi.ls_result_timestamp,'%Y-%m-%d %T') AS ls_result_timestamp,
                        twi.seqno,
                        COALESCE(trim(REGEXP_REPLACE(tar.memo, '[[:space:]]+', ' ')), '') AS memo,
                        me.error_ref_key,
                        tar.error_param,
                        tar.error_id
                    FROM
                        dfa_new.t_analysis_result AS tar
                    INNER JOIN dfa_new.t_project_history AS tph
                        ON tar.pj_history_id = tph.pj_history_id
                    LEFT JOIN dfa_new.m_error AS me
                        ON me.error_id = substring_index(tar.error_id, ',', 1)
                    LEFT JOIN dfa_new.t_asm_detail tad
                        ON tar.asm_id = tad.asm_id
                    
                    
                    
                    
                    LEFT JOIN dfa_new.t_chamber_unique_info AS tcui
                        ON tad.chamber_id = tcui.chamber_unique_id
                    LEFT JOIN dfa_new.t_chamber_recipe_info AS tcri
                        ON tad.recipe_no = tcri.recipe_no
 					
                     
                    LEFT JOIN dfa_new.t_pcsjb_info AS pj
		                ON 
		                 (
		                 tcui.device_unique_id=pj.device_unique_id  
		                 AND SUBSTRING(tad.process_recipe_name, LOCATE(' ',
                             tad.process_recipe_name)+1) = pj.seq_rcp_name                         
                    	 AND  tad.start_date   >=   pj.pj_start_time
                    	 AND  tad.end_date   <=   pj.pj_end_time                    
                         )
                     
                    LEFT JOIN dfa_new.t_device_unique_info AS tdui
                        ON tcui.device_unique_id = tdui.device_unique_id
                    LEFT JOIN dfa_new.t_wafer_info twi
                        ON tar.analysis_id = twi.analysis_id                        
                                            
                    LEFT JOIN dfa_new.t_mapping AS mpp
						ON 
						 (
						 tar.asm_id = mpp.asm_id
						 AND tar.measure_hash = mpp.measure_hash
						 )
					     
                     
         WHERE tph.project_id = 3
            
               -- AND tar.add_date = '2023-02-15 16:40:38' 
                        
                )
                SELECT t1.*, t3.edx_timestamp
                FROM t1
                LEFT JOIN (
                    WITH t2 AS (
                        SELECT
                            ROW_Number() OVER(
                                PARTITION BY t_defect_detail.wafer_seqno
                                ORDER BY t_defect_detail.defect_id ASC
                            ) AS row_num,
                            t_wafer_info.analysis_id,
                            date_format(t_defect_detail.edx_timestamp,'%Y-%m-%d %T') AS edx_timestamp
                        FROM
                            dfa_new.t_defect_detail
                        INNER JOIN dfa_new.t_wafer_info
                            ON t_defect_detail.wafer_seqno = t_wafer_info.seqno
                        WHERE t_wafer_info.seqno IN (SELECT seqno FROM t1)
                    )
                    SELECT *
                    FROM t2
                    WHERE t2.row_num = 1
                ) AS t3
                    ON t1.analysis_id = t3.analysis_id
            )
            UNION
            (
                SELECT
                    NULL AS analysis_id,
                    tae.analysis_err_id,
                    COALESCE(trim(REGEXP_REPLACE(tdui.device_name, '[[:space:]]+', ' ')), '') AS d_device_name,
                    COALESCE(trim(REGEXP_REPLACE(tcui.device_name, '[[:space:]]+', ' ')), '') AS c_device_name,
                    null AS recipe_no,
                    null AS measure_hash,
                    null AS carrier_id,
                    
                    null AS lot_id, 
                         
                    null AS wafer_no,
                    
                    
                    
                    
                    null AS add_date,
                    
                    null AS remark,
                    null AS result,
                    null AS defect_num,
                    null AS pm_time,
                    null AS recovery_time,
                    null AS mtbc,
                    null AS mwbc,
                    null AS ls_result_timestamp,
                    null AS seqno,
                    null AS memo,
                    me.error_ref_key,
                    tae.error_param,
                    tae.error_id,
                    null AS edx_timestamp
                FROM
                    dfa_new.t_analysis_error AS tae
                INNER JOIN dfa_new.t_project_history AS tph
                    ON tae.pj_history_id = tph.pj_history_id
                LEFT JOIN dfa_new.m_error AS me
                    ON me.error_id = substring_index(tae.error_id, ',', 1)
                LEFT JOIN dfa_new.t_chamber_unique_info AS tcui
                    ON tae.chamber_id = tcui.chamber_unique_id
                LEFT JOIN dfa_new.t_device_unique_info AS tdui
                    ON tcui.device_unique_id = tdui.device_unique_id
                 
         WHERE tph.project_id = 3
            
               -- AND tae.add_date = '2023-02-15 16:40:38' 
     
            )
        )
        SELECT
            *,
            CONCAT(COALESCE(analysis_err_id, ''), '-', COALESCE(analysis_id, '')) AS id
        FROM
            result
         
          
     
        ORDER BY
            add_date DESC,
            d_device_name,
            c_device_name,
            recipe_no,
            carrier_id,
            wafer_no,
            analysis_id
         
            LIMIT
                0, 20