开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜