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. Take care Sean
