0

Given this query;

select my_field1
from my_table
where my_field2 in ('five', 'six')

If i have an index on my_field2 it does not get used. Is there a way to optimize this to make use of my index?

srayner
  • 1,799
  • 4
  • 23
  • 39
  • Possible duplicate of [MySQL not using indexes with WHERE IN clause?](https://stackoverflow.com/questions/586381/mysql-not-using-indexes-with-where-in-clause) – Marvin Sep 24 '19 at 11:19
  • What is your MySQL server version ? Any performance related question requires the result of `EXPLAIN ..` statement on the query. Please add this detail to the question. Also, check if you can execute `EXPLAIN format=JSON ...`. Please add that bit to the question if you can – Madhur Bhaiya Sep 24 '19 at 11:30

1 Answers1

0

The Optimizer chooses to ignore the index when the 'cardinality' is low. This is because simply scanning the entire table is likely to be faster than bouncing back and forth between the INDEX's BTree and the data BTree.

Rick James
  • 135,179
  • 13
  • 127
  • 222