0

I have a simple analytics table which has a little over 2 Million rows and currently growing at a pace of 20k rows per day. Please check the table

CREATE TABLE IF NOT EXISTS `affiliate_analytics` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `funnel_id` int(10) unsigned NOT NULL DEFAULT '0',
  `affiliate_id` int(10) unsigned NOT NULL DEFAULT '0',
  `created_timestamp` int(10) unsigned NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `binary_ip` varbinary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `session_id_index` (`session_id`),
  KEY `funnel_timestamp_index` (`funnel_id`,`created_timestamp`),
  KEY `funnel_aff_timestamp_index` (`funnel_id`,`affiliate_id`,`created_timestamp`),
  KEY `timestamp_idx` (`created_timestamp`),
  KEY `funnel_aff_ip_timestamp_index` (`funnel_id`,`affiliate_id`,`binary_ip`,`created_timestamp`),
  KEY `uniques_impressions_index` (`funnel_id`,`created_timestamp`,`affiliate_id`,`binary_ip`)
);

The query I am trying to run on this table is

select funnel_id, 
       affiliate_id, 
       COUNT(binary_ip) as impressions, 
       COUNT(DISTINCT(binary_ip)) as uniques 
FROM affiliate_analytics 
where funnel_id IN (2, 104, 145, 308, 309, 566, 672, 1149) 
  AND created_timestamp BETWEEN 1575176400 and 1593316799 
GROUP BY funnel_id, 
         affiliate_id

But it's taking more than 6 seconds to complete and when I am running EXPLAIN and ANALYZE queries on this table, it shows that it is not using all the parts of the index for running the query.

This is the output of the EXPLAIN statement

mysql> explain select funnel_id, affiliate_id, COUNT(binary_ip) as impressions, COUNT(DISTINCT(binary_ip)) as uniques FROM affiliate_analytics where funnel_id IN (2, 104, 145, 308, 309, 566, 672, 1149) AND created_timestamp BETWEEN 1575176400 and 1593316799 GROUP BY funnel_id, affiliate_id;
+----+-------------+--------------------------------+------------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------+--------+----------+--------------------------+
| id | select_type | table                          | partitions | type  | possible_keys                                                                                                           | key                           | key_len | ref  | rows   | filtered | Extra                    |
+----+-------------+--------------------------------+------------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------+--------+----------+--------------------------+
|  1 | SIMPLE      | affiliate_analytics            | NULL       | range | funnel_timestamp_index,funnel_aff_timestamp_index,timestamp_idx,funnel_aff_ip_timestamp_index,uniques_impressions_index | funnel_aff_ip_timestamp_index | 4       | NULL | 407111 |    50.00 | Using where; Using index |
+----+-------------+--------------------------------+------------+-------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------+---------+------+--------+----------+--------------------------+

I tried forcing the index uniques_impressions_index which should ideally be faster as per the MySql Index cookbook, however, this is no better and it's still taking 5 seconds for the query to run.

This is the output of the EXPLAIN statement with the forced uniques_impressions_index

mysql> explain select funnel_id, affiliate_id, COUNT(binary_ip) as impressions, COUNT(DISTINCT(binary_ip)) as uniques FROM affiliate_analytics use index(uniques_impressions_index) where funnel_id IN (2, 104, 145, 308, 309, 566, 672, 1149) AND created_timestamp BETWEEN 1575176400 and 1593316799 GROUP BY funnel_id, affiliate_id;
+----+-------------+--------------------------------+------------+-------+------------------------------------------------------------------------------------+---------------------------+---------+------+--------+----------+------------------------------------------+
| id | select_type | table                          | partitions | type  | possible_keys                                                                      | key                       | key_len | ref  | rows   | filtered | Extra                                    |
+----+-------------+--------------------------------+------------+-------+------------------------------------------------------------------------------------+---------------------------+---------+------+--------+----------+------------------------------------------+
|  1 | SIMPLE      | affiliate_analytics            | NULL       | range | funnel_aff_timestamp_index,funnel_aff_ip_timestamp_index,uniques_impressions_index | uniques_impressions_index | 8       | NULL | 407111 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+--------------------------------+------------+-------+------------------------------------------------------------------------------------+---------------------------+---------+------+--------+----------+------------------------------------------+

I am at my wit's end with this one single query which is slowing down our app for almost 4 days now.

Any help is appreciated.

Thanks in advance!

  • mysql-5.6 is getting quite old and doesn't have all the optimization of later versions. Try putting your funnel ID IN ( ... ) list as a temporary table with a PK and JOIN on that and see if that improves the explain. – danblack Jun 29 at 1:25
  • The two indexes -- one touches fewer rows; the other avoids the filesort. It's not surprising that they took about the same length of time. "Uniques" are hard to rollup, but not impossible: mysql.rjweb.org/doc.php/uniques – Rick James Jun 29 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.