Too much time on optimal path

Question

Below query has taken just 0.04 second

SELECT A.* 
FROM  `TABLE1` 
JOIN TABLE2 ON ( TABLE1.ID = TABLE2.ID ) 
WHERE  `COL1` ='1'

While next query has taken 4 seconds

SELECT A.* FROM  `TABLE1` 
JOIN TABLE2 ON ( TABLE1.ID = TABLE2.ID ) 
WHERE  `COL1` ='1' and col2 not in (....ard 4k integers...P

and maximum time is getting spend on statistic (3sec+) which probably mean looking for optimal query path.

I am using MySQL 5.5.25 and just want to know if this is a bug.

Answer 1

This is a common limitation with mysql, it is not recommended to go with large IN clauses. Create a temporary table and then join against it. Be careful about replicating the temp table, most likely there is no need to replicate it and it can cause lag.