This is my table structure:
CREATE TABLE `channel_play_times_bar_chart` (
`playing_date` datetime NOT NULL,
`channel_report_tag` varchar(50) NOT NULL,
`country_code` varchar(50) NOT NULL,
`device_report_tag` int(11) NOT NULL,
`greater_than_30_minutes` decimal(10,0) NOT NULL,
`15_to_30_minutes` decimal(10,0) NOT NULL,
`0-15_minutes` decimal(10,0) NOT NULL,
PRIMARY KEY (`country_code`,`device_report_tag`,`channel_report_tag`,`playing_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SUM(`greater_than_30_minutes`) AS '>30 minutes',
SUM(`15_to_30_minutes`) AS '15-30 Minutes',
SUM(`0-15_minutes`) AS '0-15 Minutes'
channel_play_times_bar_chart USE INDEX (ABCDE)
WHERE country_code = 'US'
AND device_report_tag = 14
AND channel_report_tag = 'DUNYA NEWS'
AND playing_date BETWEEN '2016-09-01'
GROUP BY channel_report_tag
ORDER BY SUM(`greater_than_30_minutes`) DESC
CREATE INDEX ABCDE
ON channel_play_times_bar_chart (
It is using the key you are showing in the
create index, that is,
It would be nice if you did a
show create table channel_play_times_bar_chart
and just showed it all at once. That key might not be of much use to you as it replicates most of what your rather wide Primary Key already gives you.
Once the query uses the key up thru the 3rd segment of the composite key, it resumes with a
WHERE range on
playing_date in that composite and finds 8 rows.
EXPLAIN is an estimate.
Further, I would reconsider the strategy for your
PRIMARY KEY (
PK) ideas especially considering that you decided to dupe it up more or less with the creation of
ABCDE. That means you are maintaining two indexes with little if anything gained on the second one (you added one column to the secondary index
PK It is rather WIDE (118 bytes I believe). It dictates the physical ordering. And that idea could easily be a bad one if used throughout the way you architect things. Changes made to data via
UPDATE that impact the columns in the
PK force a reshuffle of physical ordering of the table. That fact would be a good indication why
id INT AUTO_INCREMENT PRIMARY KEY is often used as a best practice use case as it never endures a reshuffle and is THIN (4 bytes).
The width of keys and their strategy with referencing (other) tables (in Foreign Key Constraints) impact key sizes and performance for lookups. Wide keys can measurably slow down that process.
This is not to suggest that you shouldn't have a key on those columns like your secondary index
ABCDE. But in general that is not a good idea for the
Note that it could be argued that
ABCDE never gives you any benefit over your PK due to range queries ceasing the use of it near the end that just
WHERE out with ranges once it hits the date. Just a thought.
A nice read and rather brief is the article Using EXPLAIN to Write Better MySQL Queries.