|Subject||RE: Odp: [firebird-support] Indexes on subquery|
|Author||Svein Erling Tysvær|
> [Query1]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);
> select id from TEST where id2 in (1,2);
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.