Christopher Herrick wrote:

>Does Firebird support table or join hints? I can't get Firebird to use the
>appropriate index when returning results from a subquery even when there
>exists an index on the joined column. My query is fairly complex, but even
>when I break it down to the following, it won't use the index on ID2 of
>table A. Table A contains tens of thousands of rows.
>
>
>SELECT A.NAME
>FROM A
>WHERE A.ID2 IN
> (SELECT B.ID2
> FROM B, C
> WHERE B.ID1 = C.ID1
> AND C.NAME = 'FOO')
>
>Any suggestions other than rewriting the query?
>
>
>

Sorry, FB execute this type of query as correlated sub-query, that is
will be executed for every row in the A table.

Couldn't you rewrite ti to:

select
A.Name
from
A join
B on (A.ID2 = B.ID2) join
C on (C.ID1 = B.ID2)
where
C.Name = 'FOO'

this query will be way faster !

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br