0

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.

4
  • It would help to know what ind5 is. Please provide SHOW CREATE TABLE.
    – Rick James
    Sep 22 at 15:17
  • #1 MySql 5.6 is a bit old, newer versions might handle it better
    – jkavalik
    Sep 22 at 15:18
  • #2 LIMIT should not be used without ORDER BY
    – jkavalik
    Sep 22 at 15:19
  • How much of the table matches the values 1 and 10? If it's a large enough portion of the table, the optimizer skips the index because it estimates it'll be less costly to just do the table-scan. Sep 22 at 15:24
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.

1
  • Yes the row count is over 20% of total. Thanks.
    – Johnny
    Sep 22 at 18:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.