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 ;