>Hey Set!
>
>>> [Query1]
>>> select id from TEST where id2 in (select id2 from subquerytable);
>>>
>>> [Query2]
>>> select id from TEST where id2 in (1,2);
>
>> The subselect in query1 cannot benefit from any index since it doesn't have a
>> WHERE clause. More importantly Query1 is not a good query to use in
>> Firebird. The subselect is logically executed once for every record in TEST.
>> This is done because similar queries like
>>
>> select id from TEST where id2 in (select id2 from subquerytable where
>> TEST.Name = subquerytable.Name)
>>
>> would have to execute the subquery for every row in TEST.
>>
>> Rewrite your query to
>>
>> select id from TEST T where exists(select * from subquerytable sq where
>> sq.id2 = t.id2)
>>
>> This query would use an index for subquerytable.
>
>It is extraordinary for me to question the details in your posts, they are usually the 'gold standard'.
>
>But in this case I think you are off the mark.
>
>Without a filter on the TEST table which uses an indexed condition (or an ORDER BY using an index), the optimizer would always
>perform a NATURAL scan of the TEST table, since it must test each row for the presence of a match in the sub-query.
>
>There is nothing in the query criteria which would cause the optimizer to use an index for the TEST table.

Hi Sean!

'Gold standard' was long ago, the last couple of years I've more or less repeated my old answers to new questions, unfortunately blurred by my failing memory.

I agree with everything you say about TEST, I just don't think that what I wrote would indicate that an index on TEST could be used.

I simply tried to say that something like
PLAN JOIN (TEST NATURAL, SubQueryTable INDEX(ID2_Index))
is a lot better than
PLAN JOIN (TEST NATURAL, SubQueryTable NATURAL)
which I would expect for [Query1] (NATURAL on the first tuple in a PLAN is normally a lot better than NATURAL on a subsequent tuple).

Though if JOIN is a valid option (i.e. no multiple matches in SubQueryTable), that would give the optimizer the additional option of
PLAN JOIN (SubQueryTable NATURAL, TEST INDEX(ID2_Index))
and be easier to read and at least as quick as using EXISTS (well, unless the optimizer makes a mistake).

Set