At 09:30 PM 13/02/2008, you wrote:

>I am using the latest version firebird server on windows. I have a
>query like
>
>select * from TABLE_A where TABLE_A_ID in (select TABLE_A_ID from
>TABLE_B where TABLE_B.COL_B = 'something' or TABLE_B.COL_B
>= 'something2' or TABLE_B.COL_B = 'something3' or TABLE_B.COL_B
>= 'something4'...)
>
>Both TABLE_A_ID and COL_B has an index. TABLE_B has about 26000 records
>and the query statistics says more than 900000 index reads for TABLE_B
>and the query takes about a minute to complete.
>
>The more OR i have in the subquery the longer it takes.
>
>If I run the subquery seperately it returns almost instantly. Is this
>how it is suppose to work.

Yes. This is a case where multiple consecutive correlated queries are almost certainly a lot more costly than a clean inner join:

select a.* from TABLE_A a
join TABLE_B b
on
a.TABLE_A_ID = b.TABLE_A_ID
where b.COL_B in ('something', 'something2','something3','something4'...)

You'll still get an indexed read (an OR search) on each member of the IN() list but not with all the repetition that the correlated subqueries require. (b.COL_B should be indexed, of course...)

./heLen