I have a particular query that uses IN() in the where condition which pass set of IDs.
Table Name - A Primary Key - id
When 2000 ids passed to the IN() - eg: select col1,col2 from A where id IN (1,2,3,4,5,*******) , query uses the primary key(obvious index) as the index to execute the query.
When 20000 ids passed to the IN() - query ignores the primary key and runs in 70sec with a full table scan, I used index hint (use index(PRIMARY)) and with this query used the primary key and executed under 4sec.
When 200000 ids passed to the IN() - query ignores the primary key even with 'hint' and 'force' index options. Output will retrieve 6000 rows.
Total number of rows in the table A - 100 million
Further this query used to run under 5sec with 200000 ids in IN() before upgrading to MySQL5.7 from MySQL 5.6.
My Question - Is this a bug, or does the behavior of MySQL 5.7 optimizer change with the number of values passed inside IN() compared to MySQL 5.6 ?
