MySQL not using index as expected with a WHERE IN clause

Question

I have a table called mytable and I'm trying to optimize a query on it. I generated the index ind5 on it, and it has the nullable int column col_1 in first place. This query has a WHERE IN clause, which seems to throw the query optimizer completely off. Here's something that works:

explain select status from mytable use index (ind5)
    where col_1 = 10 limit 0,10'

+----+-------------+-------------+------+-------------------+-------------------+---------+-------+--------+-------+
| id | select_type | table       | type | possible_keys     | key               | key_len | ref   | rows   | Extra |
+----+-------------+-------------+------+-------------------+-------------------+---------+-------+--------+-------+
|  1 | SIMPLE      | mytable     | ref  | ind5              | ind5              | 5       | const | 718156 | NULL  |
+----+-------------+-------------+------+-------------------+-------------------+---------+-------+--------+-------+

So far so good. Now let's try:

explain select status from mytable use index (ind5)
    where col_1 in (1, 10)  limit 0,10'

+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys     | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | mytable     | ALL  | ind5              | NULL | NULL    | NULL | 1436312 | Using where |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+

What changed here? I'm using a WHERE IN, but that should be equivalent to col_1 = 1 OR col_1 = 10, so I would still expect MySQL to use the index, which it's clearly not doing. How about if I do:

explain select status from mytable use index (ind5)
    where col_1 = 10 or col_1 = 1 limit 0,10'

+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
| id | select_type | table       | type | possible_keys     | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | mytable     | ALL  | ind5              | NULL | NULL    | NULL | 1436312 | Using where |
+----+-------------+-------------+------+-------------------+------+---------+------+---------+-------------+

Lost me completely here. What happened? Why is MySQL not using ind5?

This is MySQL 5.6.

Answer 1

It sounds like col_1 is not the first (or only) column in index ind5. Or, many rows have col_1 = 10. Please provide output from

SELECT col_1, COUNT(*) FROM mytable GROUP BY col_1;

Meanwhile,

INDEX(col_1, status)

Is optimal for all three queries.

That IN and that OR are optimized the same.

Tacking status onto the index makes it "covering", hence no need to reach into the data's BTree.

Here's another way to check performance (even if "too few" rows): http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts

Note: If more than about 20% of the rows have col_1=10, the Optimizer will decide to scan the table rather than bouncing back and forth between the Index's BTree and the data's BTree.