mysql - IN clause not using index

Question

Here is the table definition

CREATE TABLE `dt_prdtime` (
  `TCompany` varchar(3) NOT NULL DEFAULT '',
  `TPerCode` varchar(8) NOT NULL,
  `TBegDateTime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '??????',
  `TQPay` int(1) NOT NULL DEFAULT '2',
  `TYear` int(4) NOT NULL,
  `TMonth` int(2) NOT NULL,
  PRIMARY KEY (`TCompany`,`TPerCode`,`TBegDateTime`),
  KEY `TMonth` (`TMonth`) USING BTREE,
  KEY `TPerCode` (`TPerCode`,`TYear`,`TMonth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

And this is data sample. This table has 10000+ records and value in TMonth field varies

+----------+----------+---------------------+-------+-------+--------+
| TCompany | TPerCode | TBegDateTime        | TQPay | TYear | TMonth |
+----------+----------+---------------------+-------+-------+--------+
| S10      | 000001   | 2016-01-02 17:33:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-02 07:48:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-03 17:39:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-03 07:30:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-04 17:49:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-04 07:54:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-05 17:50:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-05 07:36:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-06 17:37:00 |     1 |  2016 |      1 |
| S10      | 000001   | 2016-01-06 07:35:00 |     1 |  2016 |      1 |
+----------+----------+---------------------+-------+-------+--------+

With EXPLAIN, This query uses TMonth index:

SELECT * FROM dt_prdtime WHERE TMonth = 5

while this one refuses to use the index:

SELECT * FROM dt_prdtime WHERE TMonth IN (5,6)

I tested with another simple table,

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

SELECT * FROM table2 WHERE id IN (5,6)

and the index for this table was used

Can anybody explain this? Is there something wrong with dt_prdtime table?

Answer 1

I will go out on a limb and say it is because you are using the MyISAM engine.

It is working perfectly fine with INNODB as can be seen in this Answer of mine.

I will try to spook up at least 1 honorable reference on the matter.

Here, The range Join Type, clearly an INNODB focus as it is the default engine. And when not explicitly mentioned in the manual in some documentation hierarchy, it is assumed.

Note, there is nothing contiguous about the id's in my example link. Meaning, don't hyperfocus on type=range in its EXPLAIN output. The speed is arrived at via the Optimizer (the CBO).

The cardinality in my example is very high (4.3 Million). The target id counts are relatively low (1000). The index is used.

Your situation may be the opposite: your cardinality might be incredibly low, like 3, and the optimizer decides to abandon use of the index.

To check your index cardinality, see the Manual Page SHOW INDEX Syntax.

A simple call such as:

show index from ratings;

+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ratings |          0 | PRIMARY  |            1 | id          | A         |     4313544 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+