At 09:39 PM 18/12/2003 -0400, you wrote:

>Hi,
>
>At December 18, 2003, 20:41, Alan McDonald wrote:
>
> > to quote Celko
> > "An EXISTS predicate can use indexes to find.. or fail, whereas the IN
> > predicate ofen has to build the results of the subquery in a working table"
>
> > not sure exactly how FB is optimised in this regard
>
>FB 1.5 will use an index if it exists, and also depending on what is
>in the IN predicate clause. I can't say precisely when an index would
>be used, because I've always used the EXISTS predicate and never came
>across a scenario where I had to use the IN predicate.

FB 1.5 actually resolves an IN() predicate to an EXISTS() predicate if it
can, and then will use an index if a useful one is available. (I thought
this was one of Arno's 1.5 optimizations, but D. Sibiryakov thinks this
optimization was in the Fb 1.0.x engine as well..)

At this stage, in Firebird, an IN() predicate that doesn't resolve to an
EXISTS() won't use an index at all. Apparently, Yaffil implemented use of
an index for all IN() searches...

/heLen