Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
Post History
Why would the second way be faster? Generally speaking, the first form will perform worse (as well as looking a lot worse) than the second. You are hitting an edge case where the opposite is true...
Answer
#1: Initial revision
by
Jack Douglas
·
2020-08-09T17:52:27Z (about 4 years ago)
> Why would the second way be faster? Generally speaking, the first form will perform worse (as well as looking a lot worse) than the second. You are hitting an edge case where the opposite is true, because: 1. The `not in` in your first example is likely to be transformed into an anti-join (something like [this](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=4436e351646efbb4a99708f09ef9904f)). Because you also have "…just a handful of records where a bit(1) column is set to 1…" that anti-join is likely to be fairly fast. 2. Bad stats or bad luck means that the optimizer is making a wrong choice when filtering. Perhaps it is choosing a full table scan in the second case, or failing to use a good index. We'd need to know your actual plans/indexes/etc to be able to say more, as several people have mentioned in comments.