--- In firebird-support@yahoogroups.com, "cpis20022002" wrote:

> Hi Firebird people
>
> I have this select, and I want ask you, why then
> the first select don't use the index (id is the primary key)
>
> SELECT SUM(encomendas.qtdtotal) FROM encomendas
> WHERE id in (SELECT idenc FROM encgrupolin WHERE id=:xpto)
>
> Plan
> PLAN (ENCGRUPOLIN INDEX (RDB$FOREIGN214,FK_ENCGRUPOLIN_ENCOMENDAS))
> PLAN (ENCOMENDAS NATURAL)
>
> The in clause don't use indexes?

Sure, IN can use indexes. I'm sure you would have observed that if you
replaced your subselect with constants. For this particular SELECT
there is no reason why Firebird should have to evaluate the subselect
for every row, but that's what Firebird did until version 1.5 (for all
I know it might still do, I have not changed from 1.0 yet). For 1.0 it
is therefore better to change this kind of query to

SELECT SUM(encomendas.qtdtotal) FROM encomendas
WHERE exists (SELECT * FROM encgrupolin
WHERE encgrupolin.idenc = encomendas.id and
encgrupolin.id=:xpto)

Another thing is that your query is ambiguous, you should qualify your
reference to id within your subselect (in theory it could refer to
either encomendas or encgrupolin).

HTH,
Set
- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.firebirdsql.org/ff/foundation