sql - Optimal Query MySQL

Question

My query took 29 seconds to complete, I need to change the sub-query to another more optimized statement

Here My Code

SELECT   Max(`a`.`mt_sts_cd`)   AS `mt_sts_cd`, 
     max(`a`.`recevice_dt`) AS `recevice_dt`, 
     max(`a`.`wmtid`)       AS `wmtid`, 
     max(`a`.`mt_no`)       AS `mt_no`, 
     max(`b`.`mt_nm`)       AS `mt_nm`, 
              concat(( 
     CASE 
              WHEN ( 
                                max(`b`.`bundle_unit`) = 'Roll') THEN round((sum(`a`.`gr_qty`) / max(`b`.`spec`)),2)
              ELSE round(max(`a`.`gr_qty`),2) 
     END),' ', max(`b`.`bundle_unit`)) AS `qty`, 
     max(`b`.`spec`)                   AS `spec`, 
     sum(`a`.`gr_qty`)                 AS `gr_qty`, 
     max(`a`.`reg_dt`)                 AS `reg_dt`, 
     ifnull( 
     ( 
            SELECT sum(`w_material_info`.`gr_qty`) 
            FROM   `w_material_info` 
            WHERE  (( 
                                 `w_material_info`.`mt_sts_cd` = '002') 
                   AND    ( 
                                 `w_material_info`.`lct_cd` LIKE '002%') 
                   AND    ( 
                                 `w_material_info`.`mt_type` <> 'CMT') 
                   AND    ( 
                                 `w_material_info`.`mt_no` = `a`.`mt_no`))),0) AS `dsd`, 
     ifnull( 
     ( 
            SELECT sum(`w_material_info`.`gr_qty`) 
            FROM   `w_material_info` 
            WHERE  (( 
                                 `w_material_info`.`mt_sts_cd` NOT IN ('002', 
                                                                       '005',
                                                                                                '013')) 
                   AND    ( 
                                 `w_material_info`.`lct_cd` LIKE '002%') 
                   AND    ( 
                                 `w_material_info`.`mt_type` <> 'CMT') 
                   AND    ( 
                                 `w_material_info`.`mt_no` = `a`.`mt_no`))),0) AS `csd` 
FROM     (`w_material_info` `a` 
JOIN     `d_material_info` `b` 
ON      (( 
                       `a`.`mt_no` = `b`.`mt_no`))) 
WHERE    (( 
                       `a`.`lct_cd` LIKE '002%') 
     AND      ( 
                       `a`.`mt_type` <> 'CMT') 
     AND      ( 
                       `a`.`mt_sts_cd` <> '005')
     AND                    `a`.`mt_sts_cd` <> '013'
        ) 
GROUP BY `a`.`mt_no` 
ORDER BY max(`a`.`reg_dt`);

I have tried using

SELECT   MAX(`a`.`mt_sts_cd`)   AS `mt_sts_cd`, 
     max(`a`.`recevice_dt`) AS `recevice_dt`, 
     max(`a`.`wmtid`)       AS `wmtid`, 
     max(`a`.`mt_no`)       AS `mt_no`, 
     max(`b`.`mt_nm`)       AS `mt_nm`, 
              concat(( 
                         CASE 
                                  WHEN (max(`b`.`bundle_unit`) = 'Roll')
                                        THEN round((sum(`a`.`gr_qty`) / max(`b`.`spec`)),2)
                                ELSE round(max(`a`.`gr_qty`),2) 
                        END
        ),' ',
        max(`b`.`bundle_unit`)) AS `qty`, 
     max(`b`.`spec`)                   AS `spec`, 
     sum(`a`.`gr_qty`)                 AS `gr_qty`, 
     max(`a`.`reg_dt`)                 AS `reg_dt`,        
            CASE WHEN `a`.`mt_sts_cd` = '002'
                  AND  `a`.`lct_cd` LIKE '002%'
                  AND  `a`.`mt_type` <> 'CMT'
                  THEN SUM(`a`.`gr_qty`)
                  ELSE
                        0
            END AS `dsd`,
            CASE WHEN `a`.`mt_sts_cd` <> '002'
                  AND `a`.`mt_sts_cd` <> '005'
                  AND `a`.`mt_sts_cd` <> '013'
                  AND  `a`.`lct_cd` LIKE '002%'
                  AND  `a`.`mt_type` <> 'CMT'
                  THEN SUM(`a`.`gr_qty`)
                  ELSE
                        0
            END AS `csd`
FROM     (`w_material_info` `a` JOIN `d_material_info` `b` ON `a`.`mt_no` = `b`.`mt_no`
        ) 
WHERE    (( 
                       `a`.`lct_cd` LIKE '002%') 
     AND      ( 
                       `a`.`mt_type` <> 'CMT') 
     AND      ( 
                       `a`.`mt_sts_cd` <> '005')) 
     AND `a`.`mt_sts_cd` <> '013'
GROUP BY `a`.`mt_no` 
ORDER BY max(`a`.`reg_dt`)

Although the query has reduced the time to 0.5 seconds compared to 29 seconds at the beginning, but the results are not 100% correct.

Finnally: I want to convert Sub-Query to another way! Is there any way?

Answer 1

等待大神解答