query optimization

[Rick James]

(The title was too uninteresting to get any attention)

Current query...

select  count(distinct(sh.sof_no))
    from  tb_r_so_h sh
    left join  
      ( SELECT  h.DO_RETURNED_DT,h.GI_DOC_DT,d.GI_ITEM_QTY,
                d.SOF_NO
            from  tb_r_gi_d d
            inner join  tb_r_gi_h h on h.gi_no = d.gi_no ) gid on sh.SOF_NO = gid.SOF_NO
    left join  
      ( SELECT  SO_ITEM_QTY, SOF_NO
            from  tb_r_so_d) sod on sh.SOF_NO = sod.SOF_NO
    left join  
      ( SELECT  poh.PO_SEND_DT,pod.PO_ITEM_NO,pod.PO_NO,
               poh.sof_no,poh.PO_DOC_DT,pod.PO_ITEM_DELV_DT
            from  tb_r_vendor_po_h poh
            inner join  tb_r_vendor_po_d pod on pod.PO_NO = poh.PO_NO) vpoh on sh.SOF_NO = vpoh.sof_no
    left join  
      ( SELECT  GR_NO,PO_NO,PO_ITEM_NO
            from  tb_r_gr_d ) grd on (grd.PO_NO = vpoh.PO_NO)
      and  (grd.PO_ITEM_NO = vpoh.PO_ITEM_NO)
    left join  
      ( SELECT  GR_DOC_DT,GR_NO
            from  tb_r_gr_h) grh on (grh.GR_NO = grd.GR_NO)
    left join  
      ( SELECT  SOF_NO,'No' as Invoiced
            from  tb_r_so_h sh
            where  1=1
              and  NOT exists 
              ( SELECT  1
                    from  tb_r_so_invoice soi
                    where  soi.SOF_NO = sh.SOF_NO )
              and  exists 
              ( SELECT  1
                    from  tb_r_so_d sod
                    where  sod.sof_no = sh.sof_no
                      and  sod.SO_ITEM_CATEGORY in ('ZA13',
                               'ZA14','ZA15','ZA16','ZA17','ZA18',
                               'ZA19','ZA20','ZA21','ZA22','ZA23',
                               'ZA24','ZA25','ZA26','ZA27','ZA28',
                               'ZA29','ZA30','ZA31','ZA32','ZA33',
                               'ZB10','ZB11','ZB12','ZB13','ZB14',
                               'ZB15','ZB16','ZB17','ZB18','ZB19',
                               'ZB21','ZB22','ZB23','ZB24','ZB25',
                               'ZB26','ZB27','ZB28','ZN10','ZN11',
                               'ZN12','ZN13','ZN14','ZN15','ZN16',
                               'ZN17','ZN18','ZN20','ZN21','ZN22',
                               'ZN23','ZN24','ZN25','ZN26','ZN27',
                               'ZN28')) ) ni on sh.SOF_NO = ni.SOF_NO
    where  1 = 1
      and  (isNull(grh.GR_DOC_DT) = 1
              and  DATEDIFF(CURDATE(),vpoh.PO_ITEM_DELV_DT) <= 1)
      and  SOF_REC_DT_BY_SA between '1900-01-01'
      and  '9999-12-31'

For a first suggestion: Leave the EXISTs clauses alone, but turn the rest of the subqueries into JOINs. MySQL does a much better job of optimizing JOINs than subqueries.

Also helpful:
* SHOW CREATE TABLE tbl\G -- engine, indexes
* SHOW VARIABLES LIKE '%buffer%'; -- cache size