performance of "where id in(ids)"?

Question

is it okay to use the following query? how is the performance?

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

my initial test comes up very fast, but I guess it is because I am the only who is using the server right now.

Answer 1

If you have an index it can be very fast.

However there is a bug in MySQL (possibly fixed in MySQL 5.5) where if there is no index, it won't just be slow, it will be incredibly slow. This because the subquery can be detected as a DEPENDENT SUBQUERY (correlated subquery) even when it is not. You can see whether MySQL is using the correct query plan by running EXPLAIN SELECT ... and checking that key is not NULL for your subquery. I have made another post about this bug with some more details:

• Why would an IN condition be slower than “=” in sql?

You can also consider rewriting your query to use JOIN instead of IN to avoid this bug.