Why index is not used after replacing "WHERE field=x" to "field in (x,y)"? There is MariaDB 10.1.20.


Create table:

CREATE TABLE test2 (
   id INTEGER NOT NULL DEFAULT 0,
   flag enum('y','n') NOT NULL DEFAULT 'n',
   KEY(id), KEY(flag)
) ENGINE=InnoDB;

Index is used as usual:

&gt; EXPLAIN SELECT * FROM test2 WHERE id = 1;
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+
|    1 | SIMPLE      | test2 | ref  | id            | id   | 4       | const |    1 |       |
+------+-------------+-------+------+---------------+------+---------+-------+------+-------+

But why index is not used here?

&gt; EXPLAIN SELECT * FROM test2 WHERE id IN (1,2);
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | test2 | ALL  | id            | NULL | NULL    | NULL |    1 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
First, since the table is tiny (only 1 row?), you cannot trust EXPLAIN to give you information that will apply in a 'real' situation.
Indexing a flag is rarely useful.  The Optimizer looks at the situation as says (roughly):  "If I need to scan more than 20% of the table, I won't bother using the index."  This is a reasonable decision because using an index requires bouncing between the index's BTree and the data's BTree.  Whereas, scanning the table sequentially is likely to be more efficient, in spite of having to check and throw out rows that are not needed.
OK, id is not a flag.  But, to the optimizer it may as well be -- since you have only 1 row in your table?