Optimize mysql query joining table

Question

Please advice how to optimize below query and minimize query loading..

$query = "select lot.arc_id,lot.arc_mod0_kod_daerah,lot.arc_mod0_kod_mukim,lot.arc_mod0_no_dhm,lot.arc_mod0_no_lot,lot.arc_mod0_jenis_lot,lot.arc_mod0_jenis_dhm,status.arc_mod0_status_pembayar, status.arc_mod0_kod_taraf_pemilik, user.arc_mod0_no_kp_baru,user.arc_mod0_no_kp_lama,user.arc_mod0_no_syarikat,user.arc_mod0_nama, cukai.arc_mod0_cukai_tanah, cukai.arc_mod0_cukai_taliair,cukai.arc_mod0_denda,cukai.arc_mod0_notis6a,cukai.arc_mod0_tunggakan_dari,cukai.arc_mod0_tunggakan_hingga,cukai.arc_mod0_tunggakan_cukai_tanah,cukai.arc_mod0_tunggakan_taliair, cukai.arc_mod0_tunggakan_denda,cukai.arc_mod0_tunggakan_notis6a, cukai.arc_mod0_hapusan, cukai.arc_mod0_remisyen, cukai.arc_mod0_remisyen_dari, cukai.arc_mod0_remisyen_hingga, cukai.arc_mod0_status_bayar from frmmod0_lot_info lot, frmmod0_pihatpen status, frmmod0_pihak_be user, frmmod0_cukai_in cukai where lot.arc_id=status.arc_mod0_lot_id AND status.arc_mod0_pihak_id=user.arc_id AND cukai.arc_mod0_lot_id=lot.arc_id AND user.arc_mod0_no_syarikat='$company_no' AND status.arc_mod0_status_aktif='1' AND lot.arc_mod0_lotstatus = '1' AND (status.arc_mod0_kod_taraf_pemilik='01' OR status.arc_mod0_kod_taraf_pemilik='02' OR status.arc_mod0_kod_taraf_pemilik='05' OR
status.arc_mod0_kod_taraf_pemilik='06' OR status.arc_mod0_kod_taraf_pemilik='10' OR status.arc_mod0_kod_taraf_pemilik='12' OR status.arc_mod0_kod_taraf_pemilik='13' OR status.arc_mod0_kod_taraf_pemilik='14')";

This is better asked at [dba](http://dba.stackexchange.com/help/on-topic) — davejal, Jan 05 '16 at 11:39

score 0 · Answer 1 · answered Jan 05 '16 at 12:02

try this query

  select * from frmmod0_lot_info lot left join frmmod0_pihatpen status on lot.arc_id=status.arc_mod0_lot_id
  left join frmmod0_pihak_be user on status.arc_mod0_pihak_id=user.arc_id
  left join frmmod0_cukai_in cukai on cukai.arc_mod0_lot_id=lot.arc_id
  where 
  user.arc_mod0_no_syarikat='$company_no' AND 
  status.arc_mod0_status_aktif='1' AND 
  lot.arc_mod0_lotstatus = '1' AND 
  (status.arc_mod0_kod_taraf_pemilik in ('02','05','06','10','12','13','14',))

score 0 · Answer 2 · answered Jan 05 '16 at 22:47

You need these indexes:

user:   INDEX(arc_mod0_no_syarikat)
status: INDEX(arc_mod0_status_aktif, arc_mod0_pihak_id, arc_mod0_kod_taraf_pemilik)
lot:    INDEX(arc_mod0_lotstatus, arc_id)

Do EXPLAIN SELECT ... for what you have; look at the Rows column. Repeat after adding these indexes. The product of the Rows column will probably decrease dramatically.

More on creating optimal indexes.

Optimize mysql query joining table

2 Answers2