Optimalisasi Query 'NOT IN' dan 'IN'

Query yang melibatkan IN dan NOT IN dalam klausa WHERE nya terkenal sangat lambat sekali. Oleh karena itu, perlu optimalisasi. Berikut adalah contoh optimaliasi yang dapat dilakaukan.

Query dengan NOT IN

Kasus 1

Sebelum Optimalisasi


SELECT * FROM tbl_customer WHERE cstatus IN('A','B') AND ckode_depot='U10' AND ckode_customer NOT IN(SELECT ckode_customer FROM tbl_penjualan WHERE dtanggal BETWEEN '2011-12-01' AND '2011-12-31' GROUP BY ckode_customer);

Sesudah Optimalisasi

SELECT * FROM tbl_customer AS c LEFT JOIN (SELECT DISTINCT ckode_customer FROM tbl_penjualan WHERE dtanggal BETWEEN '2011-12-01' AND '2011-12-31') AS p ON c.ckode_customer=p.ckode_customer WHERE c.cstatus IN('A','B') AND c.ckode_depot = 'U10' AND p.ckode_customer IS NUll ;

Query dengan IN

Kasus 1

Sebelum Optimalisasi

SELECT sh.cnofaktur AS cnofaktur,
       sh.dtanggal AS dtanggal,
       sh.ckode_customer AS ckode_customer,
       tc.cnama AS cnama,
       IFNULL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales,
       0 ndebit,
         0 nreceipt,
           0 ncredit
FROM tbl_sales_h sh,
  (SELECT sh.cnofaktur,
          IFNULL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit
   FROM tbl_sales_h sh,
        tbl_sales_d sd
   WHERE sh.cnofaktur=sd.cnofaktur
     AND sh.ckode_customer LIKE :ckode_customer
     AND SUBSTR(sh.cnofaktur,4,1) = 'T'
     AND sh.dtanggal BETWEEN :from_date AND :to_date
   GROUP BY sh.cnofaktur) sd,
                          tbl_customer tc
WHERE sh.cnofaktur=sd.cnofaktur
  AND sh.ckode_customer LIKE :ckode_customer
  AND sh.ckode_customer = tc.ckode_customer
  AND SUBSTR(sh.cnofaktur,4,1) = 'T'
  AND sh.dtanggal BETWEEN :from_date AND :to_date
  AND sh.cnofaktur NOT IN
    ( SELECT rd.cnofaktur AS cnofaktur
     FROM tbl_cash_h rh,tbl_cash_d rd, tbl_customer tc
     WHERE rh.cno_receipt=rd.cno_receipt
       AND rh.cfrom='C'
       AND rh.ckode_from = tc.ckode_customer
       AND rh.ckode_from LIKE :ckode_customer
       AND SUBSTR(rd.cnofaktur,4,1) = 'T' )
GROUP BY sh.cnofaktur 

Hasil Explain

Lihat di alamat ini : https://gist.github.com/3753594

Sesudah Optimalisasi

SELECT sh.cnofaktur AS cnofaktur,
       sh.dtanggal AS dtanggal,
       sh.ckode_customer AS ckode_customer,
       tc.cnama AS cnama,
       IFNULL(SUM((sd.ndebit*(1+(sh.ntax/100)))+sh.nfreight),0) nsales,
       0 ndebit,
         0 nreceipt,
           0 ncredit
FROM tbl_sales_h sh  
LEFT JOIN (SELECT sh.cnofaktur,
          IFNULL(SUM((sd.nqty*sd.nprice)-sd.ndisc),0) ndebit
   FROM tbl_sales_h sh,
        tbl_sales_d sd
   WHERE sh.cnofaktur=sd.cnofaktur
     AND sh.ckode_customer LIKE '%'
     AND SUBSTR(sh.cnofaktur,4,1) = 'T'
     AND sh.dtanggal BETWEEN '2012-01-01' AND '2012-01-31'
   GROUP BY sh.cnofaktur) AS sd ON sh.cnofaktur=sd.cnofaktur
LEFT JOIN tbl_customer AS tc  ON sh.ckode_customer=tc.ckode_customer
LEFT JOIN  ( SELECT rd.cnofaktur AS cnofaktur
     FROM tbl_cash_h rh,tbl_cash_d rd, tbl_customer tc
     WHERE rh.cno_receipt=rd.cno_receipt
       AND rh.cfrom='C'
       AND rh.ckode_from = tc.ckode_customer
       AND rh.ckode_from LIKE '%'
       AND SUBSTR(rd.cnofaktur,4,1) = 'T' ) AS tch ON sh.cnofaktur=tch.cnofaktur AND tch.cnofaktur=sd.cnofaktur
  WHERE sh.ckode_customer LIKE '%'
  AND SUBSTR(sh.cnofaktur,4,1) = 'T'
  AND sh.dtanggal BETWEEN '2012-01-01' AND '2012-01-31'
  AND tch.cnofaktur IS NULL 
  GROUP BY sh.cnofaktur ;


Comments