Subject Re: [firebird-support] in vs exist
Author Arno Brinkman

> > > 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
> >
> > > 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..)

a IN predicate is handled internally as EXISTS() if the IN contains a
sub-select else every item will be converted to a OR.
FB1.0 did indeed already use EXISTS() for the IN clause with sub-query. I've
optimized situations where the optimizer didn't use a index at all and some
slowness with aggregate functions as sub-select.

> 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...

Except if the OR conditions can be computed to use a index.


should use 4 indices.

Arno Brinkman

Firebird links :

Nederlandse firebird nieuwsgroep :