Mysql – Improve performance of query with multiple group by column and count function in select

Brief Background of issue

We have a aggregate table to store rows and this table have currently 3 Million rows and we need to retrieve data from this table. Query for this data is taking too much time.

Data in this table increasing at 1 million rows/month
rate.

We are using MySQL with version 5.6

Table structure is as follows

CREATE TABLE `test` (
  `source_id` bigint(20) unsigned NOT NULL,
  `entity_id` varchar(40) NOT NULL,
  `type` int(11) NOT NULL,
  `channel_type` int(11) NOT NULL,
  `metrics_1` bigint(20) unsigned NOT NULL DEFAULT '0',
  `metrics_2` float unsigned DEFAULT NULL,
  `metrics_3` bigint(20) unsigned DEFAULT NULL,
  `posted_on` datetime NOT NULL COMMENT 'posted on',
  PRIMARY KEY (`source_id`,`posted_on`,`type`,`channel_type`,`entity_id`),
  KEY `test2` (`type`,`competitor_id`,`channel_type`,`posted_on`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE  COLUMNS(posted_on)
(PARTITION q12010 VALUES LESS THAN ('2010-04-01') ENGINE = InnoDB,
 PARTITION q22010 VALUES LESS THAN ('2010-07-01') ENGINE = InnoDB,
 PARTITION q32010 VALUES LESS THAN ('2010-10-01') ENGINE = InnoDB,
 PARTITION q42010 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
 PARTITION q12011 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
 PARTITION q22011 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
 PARTITION q32011 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
 PARTITION q42011 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
 PARTITION q12012 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
 PARTITION q22012 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
 PARTITION q32012 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB,
 PARTITION q42012 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
 PARTITION q12013 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
 PARTITION q22013 VALUES LESS THAN ('2013-07-01') ENGINE = InnoDB,
 PARTITION q32013 VALUES LESS THAN ('2013-10-01') ENGINE = InnoDB,
 PARTITION q42013 VALUES LESS THAN ('2014-01-01') ENGINE = InnoDB,
 PARTITION q12014 VALUES LESS THAN ('2014-04-01') ENGINE = InnoDB,
 PARTITION q22014 VALUES LESS THAN ('2014-07-01') ENGINE = InnoDB,
 PARTITION q32014 VALUES LESS THAN ('2014-10-01') ENGINE = InnoDB,
 PARTITION q42014 VALUES LESS THAN ('2015-01-01') ENGINE = InnoDB,
 PARTITION q12015 VALUES LESS THAN ('2015-04-01') ENGINE = InnoDB,
 PARTITION q22015 VALUES LESS THAN ('2015-07-01') ENGINE = InnoDB,
 PARTITION q32015 VALUES LESS THAN ('2015-10-01') ENGINE = InnoDB,
 PARTITION q42015 VALUES LESS THAN ('2016-01-01') ENGINE = InnoDB,
 PARTITION q12016 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB,
 PARTITION q22016 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

Primary Key for this table is

PRIMARY KEY (`source_id`,`posted_on`,`type`,`channel_type`,`entity_id`),

Query we are using to retrieve data is

SELECT 
    f.`source_id`, 
    f.`channel_type`, 
    COUNT(f.`entity_id`) AS act_count
FROM `test` AS f USE INDEX (test2)
WHERE 
    f.`source_id` IN (1, 2, 3)
    AND f.`type` = 1 
    AND f.`posted_on` BETWEEN '2014-11-06' AND '2016-02-06'
GROUP BY f.`source_type`, f.`channel_type`;

Index created for this query is

I read about indexes and created this index for the above query

KEY `test2` (`type`,`competitor_id`,`channel_type`,`posted_on`)

Output of Explain is as follows

id: 1
select_type: SELECT
table: f
type: index
possible keys: PRIMARY, test2
key: test2
key_len: 14
ref: NULL
rows: 2912247
Extra: Using where; Using index

Note

we have also create partitions on posted on date which is quarterly partition

PARTITION q22010 VALUES LESS THAN ('2010-07-01') ENGINE = InnoDB
PARTITION q32010 VALUES LESS THAN ('2010-10-01') ENGINE = InnoDB
.
.
.
PARTITION q12016 VALUES LESS THAN ('2016-04-01') ENGINE = InnoDB
PARTITION q22016 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB)

Issue is

This query execution time is 8.39 seconds even with index.

Also we need to apply this query two times with different date range. so it is almost taking 16 seconds to return data.

So Please anyone can help on this how can i get this query execution time down to 2 seconds or less.

Please note number of sources in IN query can be high (30 sources max)

Struggling with this issue from past two days.

Please Let me know if any extra information required.

Any help will be appreciated.