Performance "where id in (ids)"?

Question

Can I use the following query? how is the performance?

select * from table where id not in ( 2000 or much more ids here) 

my initial test fits very quickly, but I think this is because I'm the only one who uses the server now.

Answer 1

If you have an index, it can be very fast.

However, there is a bug in MySQL, perhaps if it is installed in MySQL 5.5), if the index is missing, it will not just be slow, it will be incredibly slow. This is because a subquery can be detected as a DEPENDENT SUBQUERY (correlated subquery), even if it is not. You can see if MySQL is using the correct query plan by running EXPLAIN SELECT ... and checking that key not NULL for your subquery. I made another entry about this error with more details:

• Why will the IN condition be slower than the "=" in sql?

You might also consider rewriting your request to use JOIN instead of IN to avoid this error.