ok
the problem is that
user is selecting the types
and i am inserting this records to a temp table
and program is using the only selected types
so, selected type is in the temp table

the sql is that
SELECT ID, NAME FROM STOCK
WHERE TYPE_ID IN (SELECT ID FROM TEMPSTOCKTYPES WHERE ACTIVE = 'Y')

----- Original Message -----
From: "Svein Erling Tysvær" <svein.erling.tysvaer@...>
To: <firebird-support@yahoogroups.com>
Sent: Saturday, February 19, 2005 9:54 PM
Subject: [firebird-support] Re: Firebird 1.5.2 and index using with
subselect


>
>
> 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
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>