| Subject | select IN subquery with lots of OR in subquery is very slow |
|---|---|
| Author | navneet77 |
| Post date | 2008-02-13T10:30:07Z |
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.
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.