>> >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. :)
>>
>> Actually, although the optimizer has undergone a lot of changes
>>since Fb 1.5 to make sense of "unsmart" SQL, I think this is still
>>true of IN() when used in the context that the caution refers to...i.e.
>>
>> ...where aColumn in ('a', 'b') will use an index
>> but
>> ......where aColumn in ('a') will not.
>
>I am not sure this is the case in FB 1.5.x. Perhaps it was 1.0.x you
>are thinking about?

I cannot remember this to have been the case at all, but admit having a flaky memory and haven't used IN much since being scared away from IN(<subselect>) a long time ago. To me, it doesn't make sence not to use an index for in('a'), whereas it is very understandable that in(select 'a' from rdb$database) wasn't able to use an index.

>You don't want to know what it looks like when you get to 1499 records
>in the in predicate.

Have you tried? It may be my frontend (DB Workbench), but whenever I've tried to prepare too complex sql statements in Firebird 1.5.4, it simply stops showing the plan at all (although the query itself may run).

>I don't think it is fair to call such SQL "unsmart". I have seen much
>unsmart SQL (and admit to writing my fair share of it), but when you
>have an arbitrary list of items you want to query (based on user
>feedback or other factors external to the dbms such as sensor
>information), you really have three options.
>
>Firstly, you can use IN. Secondly, you can create a complex mix of
>BETWEEN and OR statements (with brackets for order of operations).
>This can cause less index hits if you expect blocks of consecutive
>integers. Thirdly, you can insert the "interesting" record identifiers
>into a temporary table then join to it.

You forgot a fourth option that sometimes is a great option, simply using JOIN. It does give a different result (in older Firebird versions) if you want to return duplicate rows and your subselected table contains duplicate values as well, but in all other cases I think it should be possible to form to an equivalent result set.

Set