Hi Ibrahim!

I don't quite understand why Firebird insists on evaluating the
subselect for each row when it doesn't reference anything that is part
of the main table, but I think that is the reason why it doesn't use
an index for the STOCK table.

To use the index, use JOIN rather than IN,

i.e

SELECT S.ID, S.NAME FROM STOCK S
JOIN STOCKTYPES ST ON ST.ID = S.TYPE_ID
WHERE ST.ACTIVE = 'Y'

If the join had been on a field where duplicates could occur, you
might have had to add another bit:

AND NOT EXIST(SELECT * FROM STOCKTYPES ST2
WHERE ST2.ID = ST.ID
AND ST2.ACTIVE = 'Y'
AND ST2.PK < ST.PK)

Though don't forget that it is sometimes better not to use indexes,
and there is no way you can get this query to use an index for both
tables (well, other than creating an index for STOCKTYPES.ACTIVE, but
that would in most cases be a bad idea).

HTH,
Set

--- In firebird-support@yahoogroups.com, "ibrahim bulut" wrote:
> Hi,
> i have a problem with firebird 1.5.2 about index using.
> subqueries don't use the index with this sql code
>
> SELECT ID, NAME FROM STOCK
> WHERE TYPE_ID IN (SELECT ID FROM STOCKTYPES WHERE ACTIVE = 'Y')
>
> why this problem occurs.
>
> type_id fields in the stock table is indexed
>
> if i user this sql code firebird is using index
>
> SELECT ID, NAME FROM STOCK
> WHERE TYPE_ID = 5
>
> is there any suggestion about this subject