MySQL Forums
Forum List  »  Optimizer & Parser

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query optimization and indexing - Help needed
442
June 14, 2019 12:01AM


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.