MySQL Forums
Forum List  »  Performance

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Index creation / Query Optimization : for a group by + SUM query on mysql Parttioned Table
337
November 06, 2018 07:45AM


Sorry, only registered users may post in this forum.

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.