What is the ideal composite index for this query?

Question

What is the ideal Composite Index in MySQL for the following query on an InnoDB Table1?

SELECT ColA 
FROM Table1 
WHERE ColB = 0 
AND ColC = 0 
AND ColD = 0

ColA is the left-most Primary Key.

ColB has fewer "0" rows than ColC, and ColC has fewer "0" results than ColD.

When using the composite index (ColB, ColC, ColD) on the query above, MySQL is instead using an Intersect of Indices ColB, ColC, and ColD.

Here is the requested SHOW CREATE TABLE Table1 ; results:

CREATE TABLE `Table1` (
 `ColA` int(11) unsigned NOT NULL,
 `ColE` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColF` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColB` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColG` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColC` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColH` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColD` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColJ` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColK` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColM` mediumint(8) unsigned NOT NULL DEFAULT '0',
 `ColP` mediumint(8) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`ColA`),
 KEY `ColB` (`ColB`),
 KEY `ColG` (`ColG`),
 KEY `ColC` (`ColC`),
 KEY `ColH` (`ColH`),
 KEY `ColD` (`ColD`),
 KEY `ColJ` (`ColJ`),
 KEY `ColK` (`ColK`),
 KEY `ColB_2` (`ColB`,`ColC`,`ColD`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

INDEX(ColB, ColC, ColD)

with the columns in any order, is good. This takes care of all of the WHERE clause.

Potentially even better is a "covering" index:

INDEX(ColB, ColC, ColD, ColA)

with ColA last.

"Covering means that all the columns in the SELECT are in the index. That way, the query can be performed in the BTree for the index, and not have to jump over to the data for anything else.

If the table is InnoDB, then the first index will contain ColA. This is because any secondary index implicitly contains the PRIMARY KEY.

Even in a covering index, the order is important.

Index Merge Intersect is almost never faster than a Composite index that handles the correct columns. (I think of Index Merge as a clue that you should add a Composite index.)

More on building optimal indexes.

Do not use DESC, it is not as descriptive as SHOW CREATE TABLE !

Drop KEY ColB (ColB) -- it is now redundant.

Answer 2

That's a good key choice.

MySQL is probably using the index_merge optimization to do that intersection: http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

It can be disabled by running:

    SET [GLOBAL|SESSION] optimizer_switch="index_merge_intersection=off";

More information on this is available: https://www.percona.com/blog/2012/12/14/the-optimization-that-often-isnt-index-merge-intersection/

EDIT: Wait, do you have any other indexes on this table? Can you show us an example of:

    DESC tablename;