Re: Query optimization and indexing - Help needed
Posted by:
Pandit P
Date: June 14, 2019 12:01AM
Thanks Mr. Peter,
Below are the details.
innodb_buffer_pool_size 134217728
RAM - 16 GB
Processor - 4 vCPU
Speed - Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
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,
`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` int(9) DEFAULT NULL,
`client_category_name` varchar(200) 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,
`promotion_amount` int(7) 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_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 `sales_report_mv_reprtdte` (`report_date`),
KEY `sr_mv_cust_id_report_date` (`cust_id`,`report_date`),
KEY `srmv_usid_rptdate_chnl_buid` (`user_id`,`report_date`,`channel`,`business_unit_id`),
KEY `srmv_buid_usrid_rptdate_chnl` (`business_unit_id`,`user_id`,`report_date`,`channel`),
KEY `smv_usrid_chnnl_rptdte_buid` (`user_id`,`channel`,`report_date`,`business_unit_id`),
KEY `sr_mv_bu_id_rep_dat_usr_id_cust_id` (`business_unit_id`,`report_date`,`user_id`,`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21049729 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) */
Please advise.
Regards
Praveen