I'm trying to optimize a query but I'm seeing a weird behavior I don't understand.
I have a table contains almost 2 million records, with an index on column "status_code" which is tinyint.
When I call the query with 10 numbers in In clause, mysql do a full table scan
select * from `table` as t
where t.code in (1,2,3,4,5,6,7,8,9,10);
When I call it with 9 numbers in the IN clause, the index is used.
select * from `table` as t
where t.code in (1,2,3,4,5,6,7,8,9);
Note: Number of rows here is '9' although the query returns only 1 row.
I'm using Amazon RDS, I need to understand why is this behavior, and if there is any kind of configuration that controls this.