Red Romanov Red Romanov - 5 months ago 15
SQL Question

IN clause not using index

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

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.

You 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      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+