--- In firebird-support@yahoogroups.com, "kapsee"
<kapil.surlaker@...> wrote:

>
> I have the following schema:
>
> table keyvaluepairs(k integer, v integer)
> index keyvalue_idx on keyvaluepairs(k)
>
> table validkeys(k integer)

Shouldn't you have a foreign key on keyvaluepairs (k) references to
validkeys (k), or do you expect keyvaluepairs records that are not
validkeys?

If you have a foreign key, then such a test is unnecessary.

>
> For example, when I do a
>
> select v from keyvaluepairs where k = 1
>
> it uses the index keyvalue_idx and is pretty fast.
>
> But when I do a
>
> select v from keyvaluepairs where k in (select k from validkeys)
>
> it stops using the index and does a full scan on keyvaluepairs and

is

> slow.

Yep, the subselect is executed once for every record of the outer
query, but even so I would expect Firebird 1.5 to optimise that into
an exists. Does your validkeys table have a primary key on k, or at
least a useful index on that field?

I would expect a plan like:

PLAN (validkeys INDEX (PK_validkeys))
PLAN (keyvaluepairs NATURAL)

> I realize I could use a join instead of the nested select which

would

> fix the problem but I need to use this SQL to create a view and

using

> a join would prevent the view from being updateable.

Unless you add your own triggers to the view to make it updatable
again, which may be a better idea in this case. An inner join would
give the optimiser a much better chance. Simply create a trigger on
the view that performs the underlying table operation that needs to
occur.

>
> I tried to specify the plan
>
> select v from keyvaluepairs
> where k in (select k from validkeys)
> PLAN (keyvaluepairs INDEX (keyvalue_idx))
>
> but it said "unable to use index "

Of course it can't, that does not let it solve the problem. Do not
use specific PLAN statements except to test different paths. They
literally switch off the optimiser for the statement, and not all
options can be set in the PLAN anyway.

Adam