Index creation / Query Optimization : for a group by + SUM query on mysql Parttioned Table
Posted by:
Pandit P
Date: November 06, 2018 07:45AM
I have a partitioned table with the following schema:
CREATE TABLE sales_report (
sales_report_id bigint(22) NOT NULL AUTO_INCREMENT,
report_date date DEFAULT NULL,
cust_id int(11) DEFAULT NULL,
client_number int(11) DEFAULT NULL,
customer varchar(400) DEFAULT NULL,
user_id int(9) DEFAULT NULL,
salesman varchar(100) DEFAULT NULL,
agent_code varchar(75) DEFAULT NULL,
index_id int(9) DEFAULT NULL,
index_code varchar(25) DEFAULT NULL,
index_label varchar(200) DEFAULT NULL,
segment_id int(6) DEFAULT NULL,
segment_code varchar(10) DEFAULT NULL,
segment_label varchar(200) DEFAULT NULL,
master_id int(9) DEFAULT NULL,
master_code varchar(50) DEFAULT NULL,
master_label varchar(255) DEFAULT NULL,
channel_id int(7) DEFAULT NULL,
channel varchar(200) DEFAULT NULL,
gross_amount decimal(9,2) DEFAULT NULL,
net_amount decimal(9,2) DEFAULT NULL,
quantity int(7) DEFAULT NULL,
business_unit_id int(5) NOT NULL,
business_unit_name varchar(200) DEFAULT NULL,
PRIMARY KEY (sales_report_id,business_unit_id),
KEY sales_report_reprtdte_usrid_slsman (report_date,user_id,salesman),
KEY sr_rptdate_usrid_custid_chnl_customr_netamnt (report_date,user_id,cust_id,channel,customer, net_amount)
) ENGINE=InnoDB AUTO_INCREMENT=15303655 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY LIST COLUMNS(business_unit_id)
(PARTITION sr_p1 VALUES IN (1) ENGINE = InnoDB,
PARTITION sr_p2 VALUES IN (2) ENGINE = InnoDB,
PARTITION sr_p3 VALUES IN (3) ENGINE = InnoDB,
PARTITION sr_p4 VALUES IN (4) ENGINE = InnoDB,
PARTITION sr_p5 VALUES IN (5) ENGINE = InnoDB,
PARTITION sr_p6 VALUES IN (6) ENGINE = InnoDB,
PARTITION sr_p7 VALUES IN (7) ENGINE = InnoDB,
PARTITION sr_p8 VALUES IN (8) ENGINE = InnoDB,
PARTITION sr_p9 VALUES IN (9) ENGINE = InnoDB,
PARTITION sr_p10 VALUES IN (10) ENGINE = InnoDB,
PARTITION sr_p11 VALUES IN (11) ENGINE = InnoDB,
PARTITION sr_p12 VALUES IN (12) ENGINE = InnoDB,
PARTITION sr_p13 VALUES IN (13) ENGINE = InnoDB,
PARTITION sr_p14 VALUES IN (14) ENGINE = InnoDB) */
Is it possible to create an index in order to run the following query efficiently?
SET @@sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SELECT SQL_CALC_FOUND_ROWS name, clientnumber,salesman, channelname, salestotal, salestotalprev, variation, Ind, performance, yearsalestotal, yearsalestotalprev, yearvariation, histInd, yearperformance
from (SELECT STRAIGHT_JOIN
customer as name,client_number as clientnumber, salesman,channel AS channelname,
ROUND(SUM(CASE WHEN report_date BETWEEN '2018-10-01' AND '2018-10-30' THEN net_amount ELSE 0 END) ,0) AS salestotal,
ROUND(((SUM(CASE WHEN report_date BETWEEN '2017-10-01' AND '2017-10-31' THEN net_amount ELSE 0 END))* 21)/23,0) AS salestotalprev,
IFNULL(ROUND(((((SUM(CASE WHEN report_date BETWEEN '2018-10-01' AND '2018-10-30' THEN net_amount ELSE 0 END)) - (
((SUM(CASE WHEN report_date BETWEEN '2017-10-01' AND '2017-10-31' THEN net_amount ELSE 0 END))* 21)/23
)) / (
((SUM(CASE WHEN report_date BETWEEN '2017-10-01' AND '2017-10-31' THEN net_amount ELSE 0 END))* 21)/23
)) * 100),0),0) AS variation,
'' as Ind,
IFNULL(ROUND(((SUM(((CASE WHEN report_date between '2018-10-01' AND '2018-10-30' THEN net_amount ELSE 0 END))) / (((select SUM((CASE WHEN tcd.net IS NOT NULL THEN tcd.net ELSE 0 END)) from izmo_client_sales_target_category_detail tcd inner join izmo_client_target iut on iut.id = tcd.target_id where tcd.client_sales_category_id = 1 and iut.year = YEAR('2018-10-01') and tcd.month = MONTH('2018-10-01') and iut.account_id = cust_id ) / 23) * 21)) * 100), 0), 0) AS performance,
ROUND(SUM(CASE WHEN report_date BETWEEN '2018-01-01' AND '2018-10-30' THEN net_amount ELSE 0 END) ,0) AS yearsalestotal,
ROUND(((SUM(CASE WHEN report_date BETWEEN '2017-01-01' AND '2017-12-31' THEN net_amount ELSE 0 END))* 209) /252,0) AS yearsalestotalprev,
IFNULL(ROUND(((((SUM(CASE WHEN report_date BETWEEN '2018-01-01' AND '2018-10-30' THEN net_amount ELSE 0 END)) - ((((SUM(CASE WHEN report_date BETWEEN '2017-01-01' AND '2017-12-31' THEN net_amount ELSE 0 END))*209) /252)
)) / ((((SUM(CASE WHEN report_date BETWEEN '2017-01-01' AND '2017-12-31' THEN net_amount ELSE 0 END))*209) /252) )) * 100),0),0) AS yearvariation,
'' as histInd,
IFNULL(ROUND(((SUM(((CASE WHEN report_date between '2018-01-01' AND '2018-10-30' THEN net_amount ELSE 0 END))) / (((select SUM((CASE WHEN tcd.net IS NOT NULL THEN tcd.net ELSE 0 END)) from izmo_client_sales_target_category_detail tcd inner join izmo_client_target iut on iut.id = tcd.target_id where tcd.client_sales_category_id = 1 and iut.year = YEAR('2018-01-01') and iut.account_id = cust_id) / 252) * 209)) * 100), 0), 0) AS yearperformance
FROM
sales_report_mv force index (sr_mv_rptdate_usrid_custid_chnl_ntamnt_customr)
WHERE business_unit_id IN (3,4,5,6,7,8,10,11,12,13,14) and report_date BETWEEN '2017-01-01' AND '2018-10-30' and user_id IN(2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,22,23,24,25,168,169,170,205,27,28,29,30,32,33,35,36,38,39,40,42,44,165,171,174,175,52,54,56,57,59,61,62,63,65,66,68,70,184,185,72,74,76,78,79,80,82,84,85,87,88,90,92,93,95,97,99,100,102,103,105,107,186,187,110,112,113,115,127,128,129,130,131,132,133,134,135,136,137,192,193,203,121,122,123,124,125,126,166,178,179,81,83,86,89,91,94,96,98,101,104,106,108,180,181,196,197,198,199,200,201,202,204,64,67,69,71,73,75,77,182,183,31,34,37,41,43,45,46,47,48,49,50,51,53,55,58,60,172,173,176,177,151,152,153,154,155,156,157,158,159,160,161,162,163,164,194,195,138,139,140,141,142,143,144,145,146,147,148,149,150,188,189,190,191) group by cust_id,channel) as topflopdata WHERE yearsalestotal > 0 order by yearvariation,name LIMIT 0, 50;
We have already tried the following index, but no use:
KEY sr_rptdate_usrid_custid_chnl_customr_netamnt (report_date,user_id,cust_id,channel,customer, net_amount);
but we are getting "Using where; Using temporary; Using filesort" on the extra column.