--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 22:55 18/09/2008, you wrote:
> Nope - at least I hope not. NOT IN() can't use indexes, obviously.

Sorry to be the bearer of bad news, but even then it was an older
printed copy of your book Helen. :)

On Page 397, Expressions and Predicates, under Other Comparison
Predicators, there is a CAUTION block that starts:

[It is a common "newbie" mistake to treat the predicate "IN (<value>)"
as if it were equivalent to "= <value>" because the two are logically
equivalent, insofar as both return the same result. However, IN()
does not use an index. ...]

If that's incorrect, I hope it helps you track down the affending
characters and give them a good talking too. :)

> >The question is, does this still apply, and if it does are there any
> >plans to change/fix this to use indexes?
>
> Sort of like "Do you have any plans to stop beating your wife?" ;-)

Sorry, a bit hastily typed at the time, but basically meaning did this
ever change from what I had read. :)

>
>
> >If it does still apply, what would be the recommended equivalent
> >approach in SQL to take instead to maintain a useful level of

performance?

>
> Use IN() for a list of constants (within reason) - it resolves to a

series of Or'ed equality tests. Try something different (such as an
outer join) to do the NOT IN() logic if you possibly can (eliminating
matches using WHERE <matched key> is null).

>
> IN (subquery expression) gets resolved to an EXISTS() predicate,

which is short logic and, at worst, will walk the whole result. For
your NOT IN(), NOT EXISTS() is a near (but not exact) equivalent.
Again, the outer join will get a faster result.


Ah more good stuff to inwardly digest. I will try to make space in
the little grey cells :)


TO ALL: Thanks again for all of your advice and words folks.


Kind regards,


Scott :)