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.

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.

HTH,
Set

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