navneet77 wrote:

> I am using the latest version firebird server on windows.

Latest version (2.1) or latest stable version (2.0) ?

> 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.

Do you have an index covering both columns (b.table_a_id + b.col_b) ?

> The more OR i have in the subquery the longer it takes.

I guess that it doesn't use index. Did you read the query plan?

> If I run the subquery seperately it returns almost instantly. Is this
> how it is suppose to work.

Try rewriting the query to use EXISTS and move the ORs one level above:

select * from TABLE_A a
where exists (select 1 from table_b b1 where a.table_a_id =
b1.table_a_id and b1.col_b = 'smth1')
or exists (select 1 from table_b b2 where a.table_a_id = b2.table_a_id
and b2.col_b = 'smth2')
or exists (select 1 from table_b b3 where a.table_a_id = b3.table_a_id
and b3.col_b = 'smth3')

and see if that helps.

Or maybe something like this:

select * from TABLE_A a
where exists (
select 1 from table_b b
where a.table_a_id = b.table_a_id
and b.col_b in ('smth', 'smth2', 'smth3') );


--
Milan Babuskov
http://www.flamerobin.org