MySQL Forums
Forum List  »  Optimizer & Parser

Query optimization and indexing - Help needed
Posted by: Pandit P
Date: June 13, 2019 06:41AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Query optimization and indexing - Help needed
935
June 13, 2019 06:41AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.