--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

> I think IN has always been able to use indexes - mind you when used
> in the sense 'IN (1, 5, 3)'. As Anderson wrote, NOT IN is a
> different matter.

Ahh right. Thank you, inwardly digested. :)

> One - unfortunately - common way to use IN, is to use IN(SELECT

...), which can never use indexes and used to execute the subselect
for every possible row (needless to say, this was S L O W when
the subselect returned lots of rows). I think later versions of
Firebird try to transform such a construct into WHERE EXISTS(...), but
I doubt that is possible in all cases (haven't thought too much about
it, but it sounds difficult to cover all theoretical cases).

>
> So, used with constant values, I like IN, whereas used with

subselects, I dislike it - EXISTS (SELECT ...) is at least as easy to
read and the best thing you can hope for when using IN (SELECT ...) is
that Firebird is able to transform it into EXISTS before it executes
anything.

Not really used in this way with regards to this instance, thankfully,
but I will try to remember that as well.

Kind regards

Scott :)

> -----Original Message-----
> From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] On Behalf Of Scott Price

> Sent: 18. september 2008 14:55
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] Firebird 2.1.x Indexing
>
> Hi folks,
>
>
> I just had a brief question.
>
> I remember from Helen Borrie's book that it said that the IN()
> predicator doesn't use indexes to select the results. However, I know
> this is old data.
>
> The question is, does this still apply, and if it does are there any
> plans to change/fix this to use indexes?
>
> If it does still apply, what would be the recommended equivalent
> approach in SQL to take instead to maintain a useful level of

performance?

>
>
> Kind regards,
>
>
> Scott :)
>