performance of "where id in(ids)"?
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开发者_JS百科 am the only who is using the server right now.
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.
精彩评论