2

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.

1

type int(11) NOT NULL COMMENT '1. chatter, 2. marketing', -- Change to TINYINT UNSIGNED. This will save 3 bytes per row. Ditto for several other fields. And for those fields that are also in indexes, especially the PK, that "3" gets multiplied.

bigint(20) unsigned -- If you are not expecting more than 4 billion, save 4 bytes each by switching to INT UNSIGNED.

Your MD5 does not need to be utf8; change the column to CHARACTER SET ascii.

"daily_timestamp = DATE(posted_on)" -- it is not proper to have redundant data like this. Is there any reason not to compute the DATE when you need that?

AND f.`daily_timestamp` BETWEEN '2014-11-06' AND '2016-02-06'

Works fine as

AND f.`posted_on` BETWEEN '2014-11-06' AND '2016-02-06'

except for the ending value, which was 'wrong' in the first place. (Did you really want all '2016-02-06' to be included?) I suggest

AND f.`posted_on` >= '2014-11-06'
AND f.`posted_on`  < '2014-11-06' + INTERVAL 15 MONTH

Is there a 1:1 mapping between this id and name?

`profile_id` varchar(40) NOT NULL COMMENT 'profile id',
`profile_name` varchar(500) DEFAULT NULL COMMENT 'Profile name',

If so, create another table and move profile_name out of here.

Why are your PARTITIONing? It slows down the query you are discussing. Will you be "purging" old data? (That would be a reason to keep it.)

I mentioned several cases of shrinking the footprint of the table; these will speed things up a little because of I/O.

But for the real big speedup, build a Summary table of the monthly COUNTs of whatever is interesting. Then query that table instead. Maintain that table incrementally. The new SELECT will probably run in less than 1 second. Mor on Summary tables.

  • Hi Rick, Thanks for the reply. First, you are right about data type. that i should definitely change. Second, i did not get you on this line Your MD5 does not need to be utf8; change the column to CHARACTER SET ascii. Please can you explain this ? – Paresh Balar Feb 10 '16 at 8:29
  • Third, daily_timestamp, Actually, for other functionality i neen daily counts and for that i have taken that field seperate and have created index on that i read somewhere that GROUP BY DATE(posted_on) will not consider index even if i define index on posted_on field. so i have taken daily_timestamp seperate and doing GROUP BY (daily_timestamp) Fourth, I'm unable to understand what following condition will make different in terms of performance. AND f.posted_on` >= '2014-11-06'` AND f.posted_on` < '2014-11-06' + INTERVAL 15 MONTH` – Paresh Balar Feb 10 '16 at 8:29
  • Regarding, profile_id and profile_name, yes there is 1:1 mapping between this, it was for other purpose but i agree with you on this point and yes i'm planning seperate table for this profile data. Regarding partitions, i did not get your point on this. partitions slow down the query performance ? and yes answer to your question on purging is NO, i won't be purging old data. Lastly, yes summary tables are one option for me to satisfy my requirements but overhead of it is to maintain summary tables so i was working on query optimization if possible. – Paresh Balar Feb 10 '16 at 8:30
  • utf8 requires up to 3 bytes per character; a has is only ascii (1 byte_/_character). Since you are currently scanning the whole table; the daily_timestamp index is not useful for GROUP BY. Note <, not <=, which is what AND does. Partition discussion. Incrementally maintaining Summary tables will be a lot more efficient. – Rick James Feb 11 '16 at 5:49
  • (If those quick answers did not suffice, ask again.) – Rick James Feb 11 '16 at 5:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.