Performance issue in MYSQL 5.7
Posted by: Lekha Kumaravellu
Date: October 13, 2023 03:22AM
Date: October 13, 2023 03:22AM
Hello Everyone,
I am facing performance issue in my query , My table contains 28278163 this many no.of rows.
Problem:- My query is using one default index it self.
I created proper index for my query, and my query is using the index which I created, but I didn't found any difference in my execution time.
Before timings =1 minute.
After creating index timings = 1 minute
After that I have dropped my Index, and the default index together. after I ran the explain plan the no.of rows scanned are very huge(4573864).I observed that execution time has been reduced to 30 secs , I am wondering how could it happens
Before timings =1 minute.
After creating index timings = 30 secs
Here is the table schema
CREATE TABLE `sales` (
`sales_report_id` bigint(22) NOT NULL AUTO_INCREMENT,
`report_date` date DEFAULT NULL,
`cust_id` int(11) DEFAULT NULL,
`client_number` varchar(255) 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,
`family_id` int(9) DEFAULT NULL,
`family_code` varchar(25) DEFAULT NULL,
`family_label` varchar(200) DEFAULT NULL,
`niche_id` int(6) DEFAULT NULL,
`niche_code` varchar(10) DEFAULT NULL,
`niche_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,
`client_category_code` varchar(255) DEFAULT NULL,
`client_category_name` varchar(200) DEFAULT NULL,
`channel_id` int(7) DEFAULT NULL,
`channel` varchar(200) DEFAULT NULL,
`gross_amount` decimal(19,4) DEFAULT NULL,
`net_amount` decimal(19,4) DEFAULT NULL,
`promotion_amount` int(7) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`business_unit_id` int(5) NOT NULL,
`business_unit_name` varchar(200) DEFAULT NULL,
`report_year` int(11) GENERATED ALWAYS AS (year(`report_date`)) STORED,
`report_month` tinyint(4) GENERATED ALWAYS AS (month(`report_date`)) STORED,
`report_month_name` varchar(20) GENERATED ALWAYS AS (monthname(`report_date`)) STORED,
`report_quarter` tinyint(4) GENERATED ALWAYS AS (quarter(`report_date`)) STORED,
`report_semester` tinyint(4) GENERATED ALWAYS AS ((case when (month(`report_date`) between 1 and 6) then 1 else 2 end)) STORED,
`report_annual` tinyint(4) GENERATED ALWAYS AS ((case when (month(`report_date`) between 1 and 12) then 1 else 0 end)) STORED,
PRIMARY KEY (`sales_report_id`,`business_unit_id`),
KEY `sales_report_mv_reprtdte_usrid` (`report_date`,`user_id`),
KEY `sales_report_mv_reprtdte_usrid_slsman` (`report_date`,`user_id`,`salesman`),
KEY `sales_report_mv_reprtdte_usrid_slsman_idxcde_idxlbl_cstomr` (`report_date`,`user_id`,`salesman`,`index_code`,`index_label`,`customer`),
KEY `sr_mv_bu_id_rep_dat_usr_id_cust_id` (`business_unit_id`,`report_date`,`user_id`,`cust_id`),
KEY `sr_mv_idx_buid_ccn_rptdte_usrid` (`business_unit_id`,`client_category_name`,`report_date`,`user_id`),
KEY `srmv_idx_buid_rptdte_mstrcde_usrid_qty` (`business_unit_id`,`report_date`,`master_code`,`user_id`,`quantity`),
KEY `srmv_idx_buid_rptdte_mstrcde_usrid_netamt` (`business_unit_id`,`report_date`,`master_code`,`user_id`,`net_amount`),
KEY `srmv_idx_buid_rptdte_mstrcde_usrid_grssamt` (`business_unit_id`,`report_date`,`master_code`,`user_id`,`gross_amount`),
KEY `sr_mv_cust_id` (`cust_id`),
KEY `sr_mv_buid_usrid_rptdte_idxid_custid` (`business_unit_id`,`user_id`,`report_date`,`index_id`,`cust_id`),
KEY `sr_mv_buid_chnnlid_mstrid_rptdte_prmtnamt` (`business_unit_id`,`channel_id`,`master_id`,`report_date`,`promotion_amount`),
KEY `sr_mv_buid_chnnlid_famid_rptdte_prmtnamton` (`business_unit_id`,`channel_id`,`family_id`,`report_date`,`promotion_amount`),
KEY `sr_mv_buid_chnnlid_idxid_rptdte_prmtnamt` (`business_unit_id`,`channel_id`,`index_id`,`report_date`,`promotion_amount`),
KEY `sr_mv_buid_usrid_rptdte_idxcde_chnlid_grsamt` (`business_unit_id`,`user_id`,`report_date`,`index_code`,`channel_id`,`gross_amount`),
KEY `sr_mv_buid_usrid_rptdte_idxcde_idxlbl_slsman_grsamt` (`business_unit_id`,`user_id`,`report_date`,`index_code`,`index_label`,`salesman`,`gross_amount`),
KEY `srmv_buid_rdte_uid_cnum_cstid_idxcd_fcde_mcd_rmnth_namt_gamt_qty` (`business_unit_id`,`report_date`,`user_id`,`client_number`,`cust_id`,`index_code`,`family_code`,`master_code`,`report_month`,`net_amount`,`gross_amount`,`quantity`),
KEY `srmv_cstid_rptyr_idxcde_fmcde_buid` (`cust_id`,`report_year`,`index_code`,`family_code`,`business_unit_id`,`gross_amount`,`net_amount`),
KEY `sr_mv_ctid_uid_rdt_mid_fmid_nid_segmtid_icd_fcd_mcd_Na_qt_gamt` (`cust_id`,`user_id`,`report_date`,`master_id`,`family_id`,`niche_id`,`segment_id`,`index_code`,`family_code`,`master_code`,`net_amount`,`quantity`,`gross_amount`),
KEY `srmv_custid_cnum_buid_rdte_usrid_idxcd_fcde_mcde_gamt_qty` (`cust_id`,`client_number`,`business_unit_id`,`report_date`,`user_id`,`index_code`,`family_code`,`master_code`,`gross_amount`,`quantity`),
KEY `srmv_cstid_buid_rtdt_mstrid_fmlid_nsid_sgmtid_idxd` (`cust_id`,`business_unit_id`,`report_date`,`master_id`,`family_id`,`niche_id`,`segment_id`,`index_id`,`quantity`,`gross_amount`),
KEY `SRMV_RPTDT_BUID_USID_CHNLID_MSTRID_NET_GRS_QTY` (`report_date`,`business_unit_id`,`user_id`,`channel_id`,`cust_id`,`master_id`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `SRMV_RPTDT_USRID_CUSTID_NET_GRS_QTY` (`report_date`,`user_id`,`cust_id`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `SRMV_CUSTID_RPTDT_NET_GRS_QTY` (`cust_id`,`report_date`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `srmv_rptdt_custid_net_grs_qty` (`report_date`,`cust_id`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `srmv_buid_usrid_custid_rptdte_grssamt` (`business_unit_id`,`user_id`,`cust_id`,`report_date`,`gross_amount`),
KEY `test_lekha2` (`business_unit_id`,`user_id`,`cust_id`,`report_date`,`index_code`,`index_label`,`report_year`,`report_month`,`gross_amount`) USING BTREE,
KEY `srmvv_usrid_fmid_rportdte_buid` (`user_id`,`family_id`,`report_date`,`business_unit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28278163 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY LIST COLUMNS(business_unit_id)
(PARTITION sr_mv_p1 VALUES IN (1) ENGINE = InnoDB,
PARTITION sr_mv_p2 VALUES IN (2) ENGINE = InnoDB,
PARTITION sr_mv_p3 VALUES IN (3) ENGINE = InnoDB,
PARTITION sr_mv_p4 VALUES IN (4) ENGINE = InnoDB,
PARTITION sr_mv_p5 VALUES IN (5) ENGINE = InnoDB,
PARTITION sr_mv_p6 VALUES IN (6) ENGINE = InnoDB,
PARTITION sr_mv_p7 VALUES IN (7) ENGINE = InnoDB,
PARTITION sr_mv_p8 VALUES IN (8) ENGINE = InnoDB,
PARTITION sr_mv_p9 VALUES IN (9) ENGINE = InnoDB,
PARTITION sr_mv_p10 VALUES IN (10) ENGINE = InnoDB,
PARTITION sr_mv_p11 VALUES IN (11) ENGINE = InnoDB,
PARTITION sr_mv_p12 VALUES IN (12) ENGINE = InnoDB,
PARTITION sr_mv_p13 VALUES IN (13) ENGINE = InnoDB,
PARTITION sr_mv_p14 VALUES IN (14) ENGINE = InnoDB,
PARTITION sr_mv_p15 VALUES IN (15) ENGINE = InnoDB,
PARTITION sr_mv_p16 VALUES IN (16) ENGINE = InnoDB,
PARTITION sr_mv_p17 VALUES IN (17) ENGINE = InnoDB,
PARTITION sr_mv_p18 VALUES IN (18) ENGINE = InnoDB,
PARTITION sr_mv_p19 VALUES IN (19) ENGINE = InnoDB,
PARTITION sr_mv_p20 VALUES IN (20) ENGINE = InnoDB,
PARTITION sr_mv_p21 VALUES IN (21) ENGINE = InnoDB,
PARTITION sr_mv_p22 VALUES IN (22) ENGINE = InnoDB,
PARTITION sr_mv_p23 VALUES IN (23) ENGINE = InnoDB,
PARTITION sr_mv_p24 VALUES IN (24) ENGINE = InnoDB,
PARTITION sr_mv_p25 VALUES IN (25) ENGINE = InnoDB,
PARTITION sr_mv_p26 VALUES IN (26) ENGINE = InnoDB,
PARTITION sr_mv_p27 VALUES IN (27) ENGINE = InnoDB,
PARTITION sr_mv_p28 VALUES IN (28) ENGINE = InnoDB,
PARTITION sr_mv_p29 VALUES IN (29) ENGINE = InnoDB,
PARTITION sr_mv_p30 VALUES IN (30) ENGINE = InnoDB,
PARTITION sr_mv_p31 VALUES IN (31) ENGINE = InnoDB,
PARTITION sr_mv_p32 VALUES IN (32) ENGINE = InnoDB,
PARTITION sr_mv_p33 VALUES IN (33) ENGINE = InnoDB,
PARTITION sr_mv_p34 VALUES IN (34) ENGINE = InnoDB,
PARTITION sr_mv_p35 VALUES IN (35) ENGINE = InnoDB,
PARTITION sr_mv_p36 VALUES IN (36) ENGINE = InnoDB,
PARTITION sr_mv_p37 VALUES IN (37) ENGINE = InnoDB,
PARTITION sr_mv_p38 VALUES IN (38) ENGINE = InnoDB,
PARTITION sr_mv_p39 VALUES IN (39) ENGINE = InnoDB,
PARTITION sr_mv_p40 VALUES IN (40) ENGINE = InnoDB) */
Here is my query
SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS
concat_ws('-',index_code,index_label) as partindexcode,
business_unit_name as bu_name,
'' AS mon,
'' as subtotal,
ROUND(SUM(CASE WHEN report_date between '2022-01-01' and '2022-12-31' THEN gross_amount ELSE 0 END),0) as salestotal,
IFNULL(ROUND(((CASE WHEN MAX(YEAR(report_date)) = YEAR('2023-10-04') THEN (((SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END) ) / (196)) * (259)) ELSE (SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END)) END)/(SUM(CASE WHEN report_date between '2021-01-01' and '2021-12-31' THEN gross_amount ELSE 0 END))) * 100,0
), 0) as hist_vs_projection_perf,
'' as histInd,
'' as weight_of_line_for_total,
'' as partindex_id,'' as bu_id , 0 as report_lvl,0 as is_start
FROM
sales
WHERE
business_unit_id IN (20,31,32) and
(report_date between '2021-01-01' and '2022-12-31') and user_id in (368,370,371,376,385,388,389,486,493,524,369,380,381,382,383,384,386,387,417,530,544,598,372,373,374,375,377,378,379,488) having ((partindexcode != "") and (partindexcode IS NOT NULL)) UNION ALL
( SELECT STRAIGHT_JOIN
concat_ws('-',index_code,index_label) as partindexcode,
business_unit_name as bu_name,
MONTHNAME(report_date) AS mon,
'' as subtotal,
ROUND(SUM(CASE WHEN report_date between '2022-01-01' and '2022-12-31' THEN gross_amount ELSE 0 END)) AS salestotal,
IFNULL(ROUND(((CASE WHEN MONTH(report_date) = MONTH('2023-10-04') and MAX(YEAR(report_date)) = YEAR('2023-10-04') THEN (((SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END) ) / (2)) * (22)) ELSE (SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END)) END)/(SUM(CASE WHEN report_date between '2021-01-01' and '2021-12-31' THEN gross_amount ELSE 0 END))) * 100,0
),0) as hist_vs_projection_perf,
'' as histInd,
'' as weight_of_line_for_total,'' as partindex_id,'' as bu_id , 0 as report_lvl,0 as is_start
FROM
sales
WHERE
business_unit_id IN (20,31,32) and
(report_date between '2021-01-01' and '2022-12-31' and user_id in (368,370,371,376,385,388,389,486,493,524,369,380,381,382,383,384,386,387,417,530,544,598,372,373,374,375,377,378,379,488)) GROUP BY MONTH(report_date) ) UNION ALL ( SELECT STRAIGHT_JOIN
concat_ws('-',index_code,index_label) as partindexcode,
business_unit_name as bu_name, '' as mon ,
'' as subtotal,
ROUND(SUM(CASE WHEN report_date between '2022-01-01' and '2022-12-31' THEN gross_amount ELSE 0 END)) AS salestotal,IFNULL(ROUND(((CASE WHEN MAX(YEAR(report_date)) = YEAR('2023-10-04') THEN (((SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END) ) / (196)) * (259)) ELSE (SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END)) END)/(SUM(CASE WHEN report_date between '2021-01-01' and '2021-12-31' THEN gross_amount ELSE 0 END))) * 100,0
),0) as hist_vs_projection_perf,'' as histInd,
'' as weight_of_line_for_total,
index_id as partindex_id,
business_unit_id as bu_id ,
0 as report_lvl,
0 as is_start
FROM
sales
WHERE
business_unit_id IN (20,31,32) and
(report_date between '2021-01-01' and '2022-12-31') and user_id in (368,370,371,376,385,388,389,486,493,524,369,380,381,382,383,384,386,387,417,530,544,598,372,373,374,375,377,378,379,488)
GROUP BY index_code ) LIMIT 0, 50
Here is the index which i created
create index `test_lekha2` on sales_report_mv (`business_unit_id`,`user_id`,cust_id,`report_date`,`index_code`,index_label,`report_year`,`report_month`,`gross_amount`) USING BTREE; --
I am facing performance issue in my query , My table contains 28278163 this many no.of rows.
Problem:- My query is using one default index it self.
I created proper index for my query, and my query is using the index which I created, but I didn't found any difference in my execution time.
Before timings =1 minute.
After creating index timings = 1 minute
After that I have dropped my Index, and the default index together. after I ran the explain plan the no.of rows scanned are very huge(4573864).I observed that execution time has been reduced to 30 secs , I am wondering how could it happens
Before timings =1 minute.
After creating index timings = 30 secs
Here is the table schema
CREATE TABLE `sales` (
`sales_report_id` bigint(22) NOT NULL AUTO_INCREMENT,
`report_date` date DEFAULT NULL,
`cust_id` int(11) DEFAULT NULL,
`client_number` varchar(255) 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,
`family_id` int(9) DEFAULT NULL,
`family_code` varchar(25) DEFAULT NULL,
`family_label` varchar(200) DEFAULT NULL,
`niche_id` int(6) DEFAULT NULL,
`niche_code` varchar(10) DEFAULT NULL,
`niche_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,
`client_category_code` varchar(255) DEFAULT NULL,
`client_category_name` varchar(200) DEFAULT NULL,
`channel_id` int(7) DEFAULT NULL,
`channel` varchar(200) DEFAULT NULL,
`gross_amount` decimal(19,4) DEFAULT NULL,
`net_amount` decimal(19,4) DEFAULT NULL,
`promotion_amount` int(7) DEFAULT NULL,
`quantity` int(11) DEFAULT NULL,
`business_unit_id` int(5) NOT NULL,
`business_unit_name` varchar(200) DEFAULT NULL,
`report_year` int(11) GENERATED ALWAYS AS (year(`report_date`)) STORED,
`report_month` tinyint(4) GENERATED ALWAYS AS (month(`report_date`)) STORED,
`report_month_name` varchar(20) GENERATED ALWAYS AS (monthname(`report_date`)) STORED,
`report_quarter` tinyint(4) GENERATED ALWAYS AS (quarter(`report_date`)) STORED,
`report_semester` tinyint(4) GENERATED ALWAYS AS ((case when (month(`report_date`) between 1 and 6) then 1 else 2 end)) STORED,
`report_annual` tinyint(4) GENERATED ALWAYS AS ((case when (month(`report_date`) between 1 and 12) then 1 else 0 end)) STORED,
PRIMARY KEY (`sales_report_id`,`business_unit_id`),
KEY `sales_report_mv_reprtdte_usrid` (`report_date`,`user_id`),
KEY `sales_report_mv_reprtdte_usrid_slsman` (`report_date`,`user_id`,`salesman`),
KEY `sales_report_mv_reprtdte_usrid_slsman_idxcde_idxlbl_cstomr` (`report_date`,`user_id`,`salesman`,`index_code`,`index_label`,`customer`),
KEY `sr_mv_bu_id_rep_dat_usr_id_cust_id` (`business_unit_id`,`report_date`,`user_id`,`cust_id`),
KEY `sr_mv_idx_buid_ccn_rptdte_usrid` (`business_unit_id`,`client_category_name`,`report_date`,`user_id`),
KEY `srmv_idx_buid_rptdte_mstrcde_usrid_qty` (`business_unit_id`,`report_date`,`master_code`,`user_id`,`quantity`),
KEY `srmv_idx_buid_rptdte_mstrcde_usrid_netamt` (`business_unit_id`,`report_date`,`master_code`,`user_id`,`net_amount`),
KEY `srmv_idx_buid_rptdte_mstrcde_usrid_grssamt` (`business_unit_id`,`report_date`,`master_code`,`user_id`,`gross_amount`),
KEY `sr_mv_cust_id` (`cust_id`),
KEY `sr_mv_buid_usrid_rptdte_idxid_custid` (`business_unit_id`,`user_id`,`report_date`,`index_id`,`cust_id`),
KEY `sr_mv_buid_chnnlid_mstrid_rptdte_prmtnamt` (`business_unit_id`,`channel_id`,`master_id`,`report_date`,`promotion_amount`),
KEY `sr_mv_buid_chnnlid_famid_rptdte_prmtnamton` (`business_unit_id`,`channel_id`,`family_id`,`report_date`,`promotion_amount`),
KEY `sr_mv_buid_chnnlid_idxid_rptdte_prmtnamt` (`business_unit_id`,`channel_id`,`index_id`,`report_date`,`promotion_amount`),
KEY `sr_mv_buid_usrid_rptdte_idxcde_chnlid_grsamt` (`business_unit_id`,`user_id`,`report_date`,`index_code`,`channel_id`,`gross_amount`),
KEY `sr_mv_buid_usrid_rptdte_idxcde_idxlbl_slsman_grsamt` (`business_unit_id`,`user_id`,`report_date`,`index_code`,`index_label`,`salesman`,`gross_amount`),
KEY `srmv_buid_rdte_uid_cnum_cstid_idxcd_fcde_mcd_rmnth_namt_gamt_qty` (`business_unit_id`,`report_date`,`user_id`,`client_number`,`cust_id`,`index_code`,`family_code`,`master_code`,`report_month`,`net_amount`,`gross_amount`,`quantity`),
KEY `srmv_cstid_rptyr_idxcde_fmcde_buid` (`cust_id`,`report_year`,`index_code`,`family_code`,`business_unit_id`,`gross_amount`,`net_amount`),
KEY `sr_mv_ctid_uid_rdt_mid_fmid_nid_segmtid_icd_fcd_mcd_Na_qt_gamt` (`cust_id`,`user_id`,`report_date`,`master_id`,`family_id`,`niche_id`,`segment_id`,`index_code`,`family_code`,`master_code`,`net_amount`,`quantity`,`gross_amount`),
KEY `srmv_custid_cnum_buid_rdte_usrid_idxcd_fcde_mcde_gamt_qty` (`cust_id`,`client_number`,`business_unit_id`,`report_date`,`user_id`,`index_code`,`family_code`,`master_code`,`gross_amount`,`quantity`),
KEY `srmv_cstid_buid_rtdt_mstrid_fmlid_nsid_sgmtid_idxd` (`cust_id`,`business_unit_id`,`report_date`,`master_id`,`family_id`,`niche_id`,`segment_id`,`index_id`,`quantity`,`gross_amount`),
KEY `SRMV_RPTDT_BUID_USID_CHNLID_MSTRID_NET_GRS_QTY` (`report_date`,`business_unit_id`,`user_id`,`channel_id`,`cust_id`,`master_id`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `SRMV_RPTDT_USRID_CUSTID_NET_GRS_QTY` (`report_date`,`user_id`,`cust_id`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `SRMV_CUSTID_RPTDT_NET_GRS_QTY` (`cust_id`,`report_date`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `srmv_rptdt_custid_net_grs_qty` (`report_date`,`cust_id`,`net_amount`,`gross_amount`,`quantity`) USING BTREE,
KEY `srmv_buid_usrid_custid_rptdte_grssamt` (`business_unit_id`,`user_id`,`cust_id`,`report_date`,`gross_amount`),
KEY `test_lekha2` (`business_unit_id`,`user_id`,`cust_id`,`report_date`,`index_code`,`index_label`,`report_year`,`report_month`,`gross_amount`) USING BTREE,
KEY `srmvv_usrid_fmid_rportdte_buid` (`user_id`,`family_id`,`report_date`,`business_unit_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28278163 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY LIST COLUMNS(business_unit_id)
(PARTITION sr_mv_p1 VALUES IN (1) ENGINE = InnoDB,
PARTITION sr_mv_p2 VALUES IN (2) ENGINE = InnoDB,
PARTITION sr_mv_p3 VALUES IN (3) ENGINE = InnoDB,
PARTITION sr_mv_p4 VALUES IN (4) ENGINE = InnoDB,
PARTITION sr_mv_p5 VALUES IN (5) ENGINE = InnoDB,
PARTITION sr_mv_p6 VALUES IN (6) ENGINE = InnoDB,
PARTITION sr_mv_p7 VALUES IN (7) ENGINE = InnoDB,
PARTITION sr_mv_p8 VALUES IN (8) ENGINE = InnoDB,
PARTITION sr_mv_p9 VALUES IN (9) ENGINE = InnoDB,
PARTITION sr_mv_p10 VALUES IN (10) ENGINE = InnoDB,
PARTITION sr_mv_p11 VALUES IN (11) ENGINE = InnoDB,
PARTITION sr_mv_p12 VALUES IN (12) ENGINE = InnoDB,
PARTITION sr_mv_p13 VALUES IN (13) ENGINE = InnoDB,
PARTITION sr_mv_p14 VALUES IN (14) ENGINE = InnoDB,
PARTITION sr_mv_p15 VALUES IN (15) ENGINE = InnoDB,
PARTITION sr_mv_p16 VALUES IN (16) ENGINE = InnoDB,
PARTITION sr_mv_p17 VALUES IN (17) ENGINE = InnoDB,
PARTITION sr_mv_p18 VALUES IN (18) ENGINE = InnoDB,
PARTITION sr_mv_p19 VALUES IN (19) ENGINE = InnoDB,
PARTITION sr_mv_p20 VALUES IN (20) ENGINE = InnoDB,
PARTITION sr_mv_p21 VALUES IN (21) ENGINE = InnoDB,
PARTITION sr_mv_p22 VALUES IN (22) ENGINE = InnoDB,
PARTITION sr_mv_p23 VALUES IN (23) ENGINE = InnoDB,
PARTITION sr_mv_p24 VALUES IN (24) ENGINE = InnoDB,
PARTITION sr_mv_p25 VALUES IN (25) ENGINE = InnoDB,
PARTITION sr_mv_p26 VALUES IN (26) ENGINE = InnoDB,
PARTITION sr_mv_p27 VALUES IN (27) ENGINE = InnoDB,
PARTITION sr_mv_p28 VALUES IN (28) ENGINE = InnoDB,
PARTITION sr_mv_p29 VALUES IN (29) ENGINE = InnoDB,
PARTITION sr_mv_p30 VALUES IN (30) ENGINE = InnoDB,
PARTITION sr_mv_p31 VALUES IN (31) ENGINE = InnoDB,
PARTITION sr_mv_p32 VALUES IN (32) ENGINE = InnoDB,
PARTITION sr_mv_p33 VALUES IN (33) ENGINE = InnoDB,
PARTITION sr_mv_p34 VALUES IN (34) ENGINE = InnoDB,
PARTITION sr_mv_p35 VALUES IN (35) ENGINE = InnoDB,
PARTITION sr_mv_p36 VALUES IN (36) ENGINE = InnoDB,
PARTITION sr_mv_p37 VALUES IN (37) ENGINE = InnoDB,
PARTITION sr_mv_p38 VALUES IN (38) ENGINE = InnoDB,
PARTITION sr_mv_p39 VALUES IN (39) ENGINE = InnoDB,
PARTITION sr_mv_p40 VALUES IN (40) ENGINE = InnoDB) */
Here is my query
SELECT STRAIGHT_JOIN SQL_CALC_FOUND_ROWS
concat_ws('-',index_code,index_label) as partindexcode,
business_unit_name as bu_name,
'' AS mon,
'' as subtotal,
ROUND(SUM(CASE WHEN report_date between '2022-01-01' and '2022-12-31' THEN gross_amount ELSE 0 END),0) as salestotal,
IFNULL(ROUND(((CASE WHEN MAX(YEAR(report_date)) = YEAR('2023-10-04') THEN (((SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END) ) / (196)) * (259)) ELSE (SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END)) END)/(SUM(CASE WHEN report_date between '2021-01-01' and '2021-12-31' THEN gross_amount ELSE 0 END))) * 100,0
), 0) as hist_vs_projection_perf,
'' as histInd,
'' as weight_of_line_for_total,
'' as partindex_id,'' as bu_id , 0 as report_lvl,0 as is_start
FROM
sales
WHERE
business_unit_id IN (20,31,32) and
(report_date between '2021-01-01' and '2022-12-31') and user_id in (368,370,371,376,385,388,389,486,493,524,369,380,381,382,383,384,386,387,417,530,544,598,372,373,374,375,377,378,379,488) having ((partindexcode != "") and (partindexcode IS NOT NULL)) UNION ALL
( SELECT STRAIGHT_JOIN
concat_ws('-',index_code,index_label) as partindexcode,
business_unit_name as bu_name,
MONTHNAME(report_date) AS mon,
'' as subtotal,
ROUND(SUM(CASE WHEN report_date between '2022-01-01' and '2022-12-31' THEN gross_amount ELSE 0 END)) AS salestotal,
IFNULL(ROUND(((CASE WHEN MONTH(report_date) = MONTH('2023-10-04') and MAX(YEAR(report_date)) = YEAR('2023-10-04') THEN (((SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END) ) / (2)) * (22)) ELSE (SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END)) END)/(SUM(CASE WHEN report_date between '2021-01-01' and '2021-12-31' THEN gross_amount ELSE 0 END))) * 100,0
),0) as hist_vs_projection_perf,
'' as histInd,
'' as weight_of_line_for_total,'' as partindex_id,'' as bu_id , 0 as report_lvl,0 as is_start
FROM
sales
WHERE
business_unit_id IN (20,31,32) and
(report_date between '2021-01-01' and '2022-12-31' and user_id in (368,370,371,376,385,388,389,486,493,524,369,380,381,382,383,384,386,387,417,530,544,598,372,373,374,375,377,378,379,488)) GROUP BY MONTH(report_date) ) UNION ALL ( SELECT STRAIGHT_JOIN
concat_ws('-',index_code,index_label) as partindexcode,
business_unit_name as bu_name, '' as mon ,
'' as subtotal,
ROUND(SUM(CASE WHEN report_date between '2022-01-01' and '2022-12-31' THEN gross_amount ELSE 0 END)) AS salestotal,IFNULL(ROUND(((CASE WHEN MAX(YEAR(report_date)) = YEAR('2023-10-04') THEN (((SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END) ) / (196)) * (259)) ELSE (SUM(CASE WHEN report_date between '2022-01-01' AND '2022-12-31' THEN gross_amount ELSE 0 END)) END)/(SUM(CASE WHEN report_date between '2021-01-01' and '2021-12-31' THEN gross_amount ELSE 0 END))) * 100,0
),0) as hist_vs_projection_perf,'' as histInd,
'' as weight_of_line_for_total,
index_id as partindex_id,
business_unit_id as bu_id ,
0 as report_lvl,
0 as is_start
FROM
sales
WHERE
business_unit_id IN (20,31,32) and
(report_date between '2021-01-01' and '2022-12-31') and user_id in (368,370,371,376,385,388,389,486,493,524,369,380,381,382,383,384,386,387,417,530,544,598,372,373,374,375,377,378,379,488)
GROUP BY index_code ) LIMIT 0, 50
Here is the index which i created
create index `test_lekha2` on sales_report_mv (`business_unit_id`,`user_id`,cust_id,`report_date`,`index_code`,index_label,`report_year`,`report_month`,`gross_amount`) USING BTREE; --
Subject
Views
Written By
Posted
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.