4

Does using the in operator affect the use of indexes? For example, is it possible for WHERE id IN (10) to cause the optimizer to ignore an index?

Specifically, I'm trying to understand this answer from StackOverflow. The question asks why an index is not being utilized (in MySQL), and the answer suggests that it could be due to the use of in with only one value. Eg WHERE id IN (10) might get the index ignored, while WHERE id=10 would be fine.

I've briefly looked around on Google, and skimmed through some MySQL docs, but I can't find any reference to in affecting the optimizer's decision to use an index. Neither with a single value, nor multiple values.

Assuming that the IN values are the same datatype as the column they are comparing, can they affect the index usage?

The question that I linked to was for MySQL, but I work in other DBs, so I'd be interested in knowing if this is a universal thing to note about indexes, or if it's a quirk with MySQL.

6

If that happens in MySQL then it's a quirk to MySQL. Since you asked about other databases as well I decided to test it out on my postgres database. On a table with a little over 70 million rows it produces exactly the same explain for the following two queries (with the index):

explain select * from myschema.my_list where my_column in (232);
explain select * from myschema.my_list where my_column = 232;

which is:

'Bitmap Heap Scan on my_list  (cost=68093.11..4109456.90 rows=1656844 width=994)'
'  Recheck Cond: (my_column = 232)'
'  ->  Bitmap Index Scan on my_list_2  (cost=0.00..67678.90 rows=1656844 width=0)'
'        Index Cond: (my_column = 232)'

It doesn't diverge until I add in multiple elements in the list:

 explain select * from myschema.my_list where my_column in (232,79);
 explain select * from myschema.my_list where my_column = 232 or my_column = 79;

which is (respectively):

'Bitmap Heap Scan on my_list  (cost=172208.28..7050980.15 rows=4290987 width=994)'
'  Recheck Cond: (my_column = ANY ('{232,79}'::integer[]))'
'  ->  Bitmap Index Scan on my_list_2  (cost=0.00..171135.53 rows=4290987 width=0)'
'        Index Cond: (my_column = ANY ('{232,79}'::integer[]))'

'Bitmap Heap Scan on my_list  (cost=177390.73..7066890.07 rows=4230402 width=994)'
'  Recheck Cond: ((my_column = 232) OR (my_column = 79))'
'  ->  BitmapOr  (cost=177390.73..177390.73 rows=4290987 width=0)'
'        ->  Bitmap Index Scan on my_list_2  (cost=0.00..67678.90 rows=1656844 width=0)'
'              Index Cond: (my_column = 232)'
'        ->  Bitmap Index Scan on my_list_2  (cost=0.00..107596.63 rows=2634142 width=0)'
'              Index Cond: (my_column = 79)'

I would take his answer with a grain of salt.

  • 1
    Thanks for your time Indiri. I tested the same thing in Sybase ASE, and didn't encounter any query plan differences either. I'll wait a few days before accepting your answer, in case anybody else comes along with a fringe use case. – RToyo Aug 31 '17 at 22:09
2

IN with a single item turns into = as one of the Optimizer's first "steps".

Your referenced https://stackoverflow.com/questions/45989068/why-isnt-mysql-using-the-index-on-our-table ("this answer") is not about failing to use an index for IN; it is more complex than that.

And your question is more complex than you realize.

There are many reasons for the Optimizer to not use an index for IN. But the emphasis should be on not use an index not on for IN.

If you have a particular example we can discuss it.

Furthermore

From the Operator_trace, you can probably see "steps" including something like "expanded_query": "/ select#1 / select tbl.col2 AS col2 from tbl where (tbl.col1 = 123456)" as the "expansion" of ... where col1 IN(123456).

  • My comment asking for clarification got deleted, and I'm not sure why. You put thought into your answer, so I want to explain that my question is not about the query in the question I linked to. I simply linked to the claim in the answer, to provide context as to why I would ask this question. Based on you saying "the emphasis should be on not use an index not on for IN", I take it that IN on its own does not affect the choice of index, and that any optimization problem would be the same for both id IN (10, 20) as id=10 or id=20? In other words, an answer of "no" to my Q? – RToyo Sep 6 '17 at 15:12
  • WHERE id IN (10) and WHERE id IN (10,20) go down different paths in the Optimizer. The first is equivalent to id=10 which opens up a lot of good optimization doors. The second (same as (id=10 OR id=20)) is quite messy to optimize. The Accepted Answer spells out how some non-MySQL engine operates differently; it is less obvious with MySQL's EXPLAIN. – Rick James Sep 6 '17 at 16:54

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.