title: Mysql-query-with-groupby-is-extremely-slow
body: <p>I have a database using the following schema:</p>
  <pre><code>       CREATE TABLE `sls_report` (
        `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` varchar(200) 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,
        `bu_unit_id` int(5) NOT 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,
         PRIMARY KEY (`report_id`,`bu_unit_id`),
         KEY `sales_report_mv_reprtdte_usrid_slsman_idxcde_idxlbl_cstomr`                 
        (`report_date`,`user_id`,`salesman`,`index_code`,`index_label`,`customer`),
        KEY `srmv_buid_usrid_rptdate_chnl` (`bu_unit_id`,`user_id`,`report_date`,`channel`),
        KEY `sr_mv_custid_clntnum_customer_rptdte_grssamnt` 
        (`cust_id`,`client_number`,`customer`,`report_date`,`gross_amount`),
        KEY `sr_mv_buid_usrid_rptdte_custid_netamt_clntnum_cstmr` 
        (`bu_unit_id`,`user_id`,`report_date`,`cust_id`,`net_amount`,`client_number`,`customer`),
        KEY `sr_mv_buid_usrid_rptdate_idxcde_netamt` 
        (`bu_unit_id`,`user_id`,`report_date`,`index_code`,`net_amount`),
        KEY `sr_mv_buid_usrid_rptdate_idxcde_clntcatcde_netamt` 
        (`bu_unit_id`,`user_id`,`report_date`,`index_code`,`client_category_code`,`net_amount`),
        KEY `sr_mv_buid_usrid_rptdate_idxcode_qty` 
       (`bu_unit_id`,`user_id`,`report_date`,`index_code`,`quantity`)
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8
       /*!50500 PARTITION BY LIST  COLUMNS(bu_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) */
  </code></pre>
  <p>I have a database using the following schema:</p>
  <p>The table is very large and having partitions, it contains 4.5 million rows and growing further. I am running a query on it, trying to extract a report.</p>
  <p>A query like this</p>
  <pre><code>  SELECT sales_report_id, report_year, report_month, report_month_name,
           cust_id, client_number, customer, user_id, salesman, index_id,  
           index_code, index_label,family_id, family_code,family_label,
           niche_id,niche_code, niche_label,segment_id,segment_code, 
           segment_label, master_id,master_code, master_label, 
           client_category_code, client_category_name, channel_id, 
           channel, sum(gross_amount), sum(net_amount),sum(promotion_amount),
           sum(quantity), bu_unit_id, 'INDEXID' 
  from sls_report 
  GROUP BY index_id, report_year, report_month, segment_id, niche_id,
           family_id, user_id, bu_unit_id,channel_id
  </code></pre>
  <p>It is taking more than 30 minutes.  Query does not have any where clause and is having only GROUP BY clauses.</p>
  <p>I tried adding a composite index on the &quot;index_id,report_year,report_month,segment_id,niche_id,family_id, user_id&quot;.</p>
  <p>But the index is not getting used.</p>
  <p>Please advise.</p>
  <p>Regards
  Thanks in advance.</p>