I read various blogs and documents online but just wanted to know how i can optimize the query. I am unable to decide if we have to rewrite the query or add indexes in order to optimize.
I tried adding indexes also on INDEX(DSR_BOOKED_BY, dsr_booking_date,
dsr_cnno, dsr_status, dsr_cnno, dsr_dist_pin, id) but no luck.
The RAM size of the server is 16GB and innodb bufferpool size is 12GB
This is the query which is taking almost 8 hrs but no result in the end
select ob.BRANCH_CITY orig_city,dstb.BRANCH_CITY dest_city,round(sum(CASE WHEN left(dsr_cnno, 1) IN ('V', 'E', 'X') THEN
IFNULL(value, 0) ELSE 0 END),2) Premium,
round(sum(CASE WHEN left(dsr_cnno, 1) NOT IN ('V', 'E', 'X') THEN IFNULL(value, 0) ELSE 0 END),2) Non_Premium
from ( select DSR_BRANCH_CODE,dsr_cnno,DSR_AMT,
((dsr_amt) +((((dsr_amt)-ifnull((select max(ndsr_ins_amt) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)-ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -ifnull((select sum(dr_extra_amt) from dsr_revenue where dr_cnno=dsr_cnno ),0))-(((dsr_amt)-ifnull((select max(ndsr_ins_amt) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0) -ifnull((select max(ndsr_serv_charge) from ndx_dsr_table where ndsr_cnno=dsr_cnno ),0)
-ifnull((select sum(dr_extra_amt) from dsr_revenue where dr_cnno=dsr_cnno ),0) )*ifnull(((select fr_discount from fr_mas where fr_Code=dsr_cust_code)),0)/100)) *ifnull(((select (case when dsr_invdate <'2017-05-01' then ifnull(fr_fsc_per,0) else 30 end) from fr_mas where fr_code=dsr_cust_code limit 1)),0)/100)) as value, dsr_cust_code
,dsr_dest_pin from dsr_table d where dsr_booking_date BETWEEN '2017-05-01' AND '2017-06-30' AND LENGTH(dsr_cnno)=9
AND DSR_BOOKED_BY ='F' AND dsr_status<>'R' AND dsr_cnno NOT LIKE 'J%' AND dsr_cnno NOT LIKE '@%'
AND dsr_cnno NOT LIKE '576%' AND dsr_cnno NOT LIKE 'I3%' AND dsr_cnno NOT LIKE '7%'
AND dsr_cnno NOT LIKE 'N%' and d.dsr_dest_pin>0) zz
inner join fr_mas f on f.FR_CODE=zz.dsr_cust_code
inner join branch_mas ob on ob.BRANCH_CODE=zz.dsr_branch_code and ob.BRANCH_LOC='L'
inner join serv_dest_mas dm on dm.SERV_PIN=zz.dsr_dest_pin
inner join branch_mas dstb on dstb.BRANCH_CODE=dm.SERV_BRANCH and dstb.BRANCH_LOC='L'
where dstb.BRANCH_CITY in ('HYD','DEL','AMD','GGN','BLR','PNQ','MUM','CHE','CCU','NOD')
and ob.BRANCH_CITY in('HYD','DEL','AMD','GGN','BLR','PNQ','MUM','CHE','CCU','NOD')
group by orig_city,dest_city ;
and the explain plan output is
id select_type table type possible_keys key key_len ref rows Extra
------ ------------------ ------------- ------ ------------------------------------------------------------------ ------------------------ ------- ------------------------- --------- ---------------------------------------------------------------------
1 PRIMARY ob range PRIMARY,FK_BRM_CITYMAS FK_BRM_CITYMAS 4 (NULL) 10 Using index condition; Using where; Using temporary; Using filesort
1 PRIMARY <derived2> ref <auto_key2> <auto_key2> 3 billingdb.ob.BRANCH_CODE 319051 Using where
1 PRIMARY f eq_ref PRIMARY PRIMARY 9 zz.dsr_cust_code 1 Using index
1 PRIMARY dm ref PRIMARY,IDX_SDM_SERVPIN_STATUS1 IDX_SDM_SERVPIN_STATUS1 3 zz.dsr_dest_pin 2 Using index
1 PRIMARY dstb eq_ref PRIMARY,FK_BRM_CITYMAS PRIMARY 3 billingdb.dm.SERV_BRANCH 1 Using where
2 DERIVED d ref idx_dsr_bkdate,idx_dsr_bookdby_ccd_cnno,idx_dsr_bkdby_ccd_bkd_stat idx_dsr_bookdby_ccd_cnno 1 const 182365315 Using index condition; Using where
10 DEPENDENT SUBQUERY fr_mas eq_ref PRIMARY PRIMARY 9 billingdb.d.DSR_CUST_CODE 1 (NULL)
9 DEPENDENT SUBQUERY fr_mas eq_ref PRIMARY PRIMARY 9 billingdb.d.DSR_CUST_CODE 1 (NULL)
8 DEPENDENT SUBQUERY dsr_revenue ref PRIMARY PRIMARY 12 billingdb.d.DSR_CNNO 1 (NULL)
7 DEPENDENT SUBQUERY ndx_dsr_table eq_ref PRIMARY PRIMARY 12 billingdb.d.DSR_CNNO 1 (NULL)
6 DEPENDENT SUBQUERY ndx_dsr_table eq_ref PRIMARY PRIMARY 12 billingdb.d.DSR_CNNO 1 (NULL)
5 DEPENDENT SUBQUERY dsr_revenue ref PRIMARY PRIMARY 12 billingdb.d.DSR_CNNO 1 (NULL)
4 DEPENDENT SUBQUERY ndx_dsr_table eq_ref PRIMARY PRIMARY 12 billingdb.d.DSR_CNNO 1 (NULL)
3 DEPENDENT SUBQUERY ndx_dsr_table eq_ref PRIMARY PRIMARY 12 billingdb.d.DSR_CNNO 1 (NULL)
