Search code examples
mysqloptimizationindexingexplain

MySQL : Using EXPLAIN keyword to know about indexing. (Specific Use Case)

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;

When I run the following query:

EXPLAIN EXTENDED 
SELECT 
  channel_report_tag,
  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' 
FROM
  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' 
  AND '2016-09-13' 
GROUP BY channel_report_tag 
ORDER BY SUM(`greater_than_30_minutes`) DESC 
LIMIT 10

This is the output I get ( open it in another tab):

result set

The index was defined as : 

CREATE INDEX ABCDE 
ON channel_play_times_bar_chart (
  `country_code`,
  `device_report_tag`,
  `channel_report_tag`,
  `playing_date`,
  `greater_than_30_minutes`
)

I am a bit confused here ; The key column shows ABCDE being used the as the index , yet ref column shows NULL. What does this mean ? Is the index actually being used ? If not what did I do wrong ?

Solution

  • It is using the key you are showing in the create index, that is, ABCDE. 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.

    Note 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 ABCDE).

    The 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 PK.

    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.