Query optimization and indexing - Help needed

[Pandit P]

Hello everyone,

I have a querry on a partitioned table (list partition) which has some 150 million rows.

Query also contains some sub queries to compute the values based on some conditions. I have provided the query below. It is consuming almost 1.5 minutes, where in the I expect it to show the results in max 10 seconds

Query
-----

			SELECT 
				salesman,
				report_monthname AS mon, 
				IFNULL(ROUND((SUM(CASE WHEN (index_code in (1,2,3,4,6,8,9,12)) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) + (SUM(CASE WHEN (family_code IN ('ABAX','ACC1','ACT1','ACTI','DA1','ALTE','ALTO','ANIA','ATAC','Athu','ATR1','B097','B2CD','B98P','BAR1','BARD','BAS1','BOU','CEES','CIP1','VITM','PROM','PTS1','QUIP','R097','REST','SAMO','SAS','SBCI','SCHR','SK01','SOD1','SODI','STAR','SUZ','TIF1','TOT1','TOTA','TPA1','JOB','TUNA','TURB','UNI','KINGDOM1','UNIC','VAN1','VBS1','TO','D097','D98P','DAN1','DEMA','DIPR','DIVF','DURI','E097','EXT','F097','F98P','FABE','KAPM','FLAU','FORD','FTHU','GALE','H097','Haze','HYU1','IDFP','IMAW','INN1','VSF','INTE','IVES','JTPB','KITP','KSTL','L097','M097','MALA','CDM1','MEHA','MOBI','MOT1','MTS','MTS1','NCON','NCUR','NORT','OPEL','PARB','PIL1','PLEX','PNE1','PP30','PPD','PR01','PR02','B98P','BARD','INN1','SUZ')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END))) - SUM(CASE WHEN (family_code IN ('MOBI','TOTA')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) ),0) AS salestotal,IFNULL(ROUND((select SUM(istcd.net) from  izmo_user_sales_target_category_detail istcd inner join izmo_user_target iut on iut.id = istcd.target_id  where  iut.year = 2019 and iut.salesman_id IN (138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,198,199,202,203,204,205,206,207,208,209,213,214,215,216,217,218,219,220,221,222,224,228,229) and istcd.usr_sales_category_id in (9)),0),0) as goalTotal,
				IFNULL(ROUND(((CASE WHEN MAX(report_year) = YEAR('2019-06-11') THEN ((((SUM(CASE WHEN (index_code in (1,2,3,4,6,8,9,12)) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) + (SUM(CASE WHEN (family_code IN ('ABAX','ACC1','ACT1','ACTI','DA1','ALTE','ALTO','ANIA','ATAC','Athu','ATR1','B097','B2CD','B98P','BAR1','BARD','BAS1','BOU','CEES','CIP1','VITM','PROM','PTS1','QUIP','R097','REST','SAMO','SAS','SBCI','SCHR','SK01','SOD1','SODI','STAR','SUZ','TIF1','TOT1','TOTA','TPA1','JOB','TUNA','TURB','UNI','KINGDOM1','UNIC','VAN1','VBS1','TO','D097','D98P','DAN1','DEMA','DIPR','DIVF','DURI','E097','EXT','F097','F98P','FABE','KAPM','FLAU','FORD','FTHU','GALE','H097','Haze','HYU1','IDFP','IMAW','INN1','VSF','INTE','IVES','JTPB','KITP','KSTL','L097','M097','MALA','CDM1','MEHA','MOBI','MOT1','MTS','MTS1','NCON','NCUR','NORT','OPEL','PARB','PIL1','PLEX','PNE1','PP30','PPD','PR01','PR02','B98P','BARD','INN1','SUZ')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END))) - SUM(CASE WHEN (family_code IN ('MOBI','TOTA')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END)) / (113)) * (258)) ELSE ((SUM(CASE WHEN (index_code in (1,2,3,4,6,8,9,12)) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) + (SUM(CASE WHEN (family_code IN ('ABAX','ACC1','ACT1','ACTI','DA1','ALTE','ALTO','ANIA','ATAC','Athu','ATR1','B097','B2CD','B98P','BAR1','BARD','BAS1','BOU','CEES','CIP1','VITM','PROM','PTS1','QUIP','R097','REST','SAMO','SAS','SBCI','SCHR','SK01','SOD1','SODI','STAR','SUZ','TIF1','TOT1','TOTA','TPA1','JOB','TUNA','TURB','UNI','KINGDOM1','UNIC','VAN1','VBS1','TO','D097','D98P','DAN1','DEMA','DIPR','DIVF','DURI','E097','EXT','F097','F98P','FABE','KAPM','FLAU','FORD','FTHU','GALE','H097','Haze','HYU1','IDFP','IMAW','INN1','VSF','INTE','IVES','JTPB','KITP','KSTL','L097','M097','MALA','CDM1','MEHA','MOBI','MOT1','MTS','MTS1','NCON','NCUR','NORT','OPEL','PARB','PIL1','PLEX','PNE1','PP30','PPD','PR01','PR02','B98P','BARD','INN1','SUZ')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END))) - SUM(CASE WHEN (family_code IN ('MOBI','TOTA')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END)) END)/(select SUM(istcd.net) from  izmo_user_sales_target_category_detail istcd inner join izmo_user_target iut on iut.id = istcd.target_id inner join oro_user ou on iut.salesman_id = ou.id where  iut.year = 2019 and iut.salesman_id IN (138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,198,199,202,203,204,205,206,207,208,209,213,214,215,216,217,218,219,220,221,222,224,228,229) and istcd.usr_sales_category_id in (9))) * 100,0),0) as proj_vs_target,
				IFNULL(ROUND((((CASE WHEN MAX(report_year) = YEAR('2019-06-11') THEN ((((SUM(CASE WHEN (index_code in (1,2,3,4,6,8,9,12)) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) + (SUM(CASE WHEN (family_code IN ('ABAX','ACC1','ACT1','ACTI','DA1','ALTE','ALTO','ANIA','ATAC','Athu','ATR1','B097','B2CD','B98P','BAR1','BARD','BAS1','BOU','CEES','CIP1','VITM','PROM','PTS1','QUIP','R097','REST','SAMO','SAS','SBCI','SCHR','SK01','SOD1','SODI','STAR','SUZ','TIF1','TOT1','TOTA','TPA1','JOB','TUNA','TURB','UNI','KINGDOM1','UNIC','VAN1','VBS1','TO','D097','D98P','DAN1','DEMA','DIPR','DIVF','DURI','E097','EXT','F097','F98P','FABE','KAPM','FLAU','FORD','FTHU','GALE','H097','Haze','HYU1','IDFP','IMAW','INN1','VSF','INTE','IVES','JTPB','KITP','KSTL','L097','M097','MALA','CDM1','MEHA','MOBI','MOT1','MTS','MTS1','NCON','NCUR','NORT','OPEL','PARB','PIL1','PLEX','PNE1','PP30','PPD','PR01','PR02','B98P','BARD','INN1','SUZ')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END))) - SUM(CASE WHEN (family_code IN ('MOBI','TOTA')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) ) / (113)) * (258)) ELSE ((SUM(CASE WHEN (index_code in (1,2,3,4,6,8,9,12)) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END) + (SUM(CASE WHEN (family_code IN ('ABAX','ACC1','ACT1','ACTI','DA1','ALTE','ALTO','ANIA','ATAC','Athu','ATR1','B097','B2CD','B98P','BAR1','BARD','BAS1','BOU','CEES','CIP1','VITM','PROM','PTS1','QUIP','R097','REST','SAMO','SAS','SBCI','SCHR','SK01','SOD1','SODI','STAR','SUZ','TIF1','TOT1','TOTA','TPA1','JOB','TUNA','TURB','UNI','KINGDOM1','UNIC','VAN1','VBS1','TO','D097','D98P','DAN1','DEMA','DIPR','DIVF','DURI','E097','EXT','F097','F98P','FABE','KAPM','FLAU','FORD','FTHU','GALE','H097','Haze','HYU1','IDFP','IMAW','INN1','VSF','INTE','IVES','JTPB','KITP','KSTL','L097','M097','MALA','CDM1','MEHA','MOBI','MOT1','MTS','MTS1','NCON','NCUR','NORT','OPEL','PARB','PIL1','PLEX','PNE1','PP30','PPD','PR01','PR02','B98P','BARD','INN1','SUZ')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END))) - SUM(CASE WHEN (family_code IN ('MOBI','TOTA')) AND report_date between '2019-01-01' and '2019-12-31' THEN net_amount ELSE 0 END)) END))/(
				((SUM(CASE WHEN (index_code in (1,2,3,4,6,8,9,12)) AND report_date between '2018-01-01' and '2018-12-31' THEN net_amount ELSE 0 END) + (SUM(CASE WHEN (family_code IN ('ABAX','ACC1','ACT1','ACTI','DA1','ALTE','ALTO','ANIA','ATAC','Athu','ATR1','B097','B2CD','B98P','BAR1','BARD','BAS1','BOU','CEES','CIP1','VITM','PROM','PTS1','QUIP','R097','REST','SAMO','SAS','SBCI','SCHR','SK01','SOD1','SODI','STAR','SUZ','TIF1','TOT1','TOTA','TPA1','JOB','TUNA','TURB','UNI','KINGDOM1','UNIC','VAN1','VBS1','TO','D097','D98P','DAN1','DEMA','DIPR','DIVF','DURI','E097','EXT','F097','F98P','FABE','KAPM','FLAU','FORD','FTHU','GALE','H097','Haze','HYU1','IDFP','IMAW','INN1','VSF','INTE','IVES','JTPB','KITP','KSTL','L097','M097','MALA','CDM1','MEHA','MOBI','MOT1','MTS','MTS1','NCON','NCUR','NORT','OPEL','PARB','PIL1','PLEX','PNE1','PP30','PPD','PR01','PR02','B98P','BARD','INN1','SUZ')) AND report_date between '2018-01-01' and '2018-12-31' THEN net_amount ELSE 0 END))) - SUM(CASE WHEN (family_code IN ('MOBI','TOTA')) AND report_date between '2018-01-01' and '2018-12-31' THEN net_amount ELSE 0 END))
				))*100,0),0) as proj_vs_history,
				'' as goalInd , '' as salesmanid,
				1 as sales_type
				      
			FROM
				sales_report force index (sr_mv_buid_ccn_rptdte_usrid)
			WHERE
                business_unit_id IN (1) and 
				 client_category_name IN ('AGT','AG1','EUR')  and 
				(report_date >= '2018-01-01' AND report_date <= '2019-12-31') and user_id in (138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,198,199,202,203,204,205,206,207,208,209,213,214,215,216,217,218,219,220,221,222,224,228,229)
                AND CASE WHEN 'none' <> 'none' THEN salesman like CONCAT('%', 'none' , '%') ELSE 1=1 END  having ((salesman IS NOT NULL) and (salesman != ""));

indexes have been added on


I have created the indexes on sales_report_mv table with

1. business_unit_id, client_category_name, report_date and user_id (index: sr_mv_buid_ccn_rptdte_usrid)

2. business_unit_id, client_category_name, report_date, user_id, salesman

I am not sure whether the composite indexes should be added for the case statement along with the where clauses or they should be separate indexes.

partitioned column name is "business_unit_id".

Query is running very slow.

When I do the explain plan it shows

key key_len ref rows filtered Extra
sr_mv_buid_rptdte_usrid 616 (null) 680269 50 Using index condition; Using where
IDX_CF87B18C75087CD5 5 const 1 100 Using where
PRIMARY 4 distrigoboost-PP.istcd.target_id 1 5 Using where
PRIMARY 4 distrigoboost-PP.iut.salesman_id 1 100 Using index
IDX_CF87B18C75087CD5 5 const 1 100 Using where
PRIMARY 4 distrigoboost-PP.istcd.target_id 1 5 Using where

Please advise.

Regards
P Pandit