--- In ib-support@y..., "albertoperez19" <alberto19@b...> wrote:

> CREATE INDEX SES_CONT ON SESION(CODEMP, CONTROL, F_TRASLADO,
> H_TRASLADO);
> this table has about 80.000 records for each "CODEMP"
> if I make a query how
> select * from sesion where codemp=1 and c_traslado=345
> the optimizer uses the index ses_cont and is wrong because if
> i use a plan ...
> select * from sesion where codemp=1 and c_traslado=345
> PLAN ( SESION INDEX ( RDB$PRIMARY17 ))
> then the speed to execute is ten times faster.

Howdy.

This is common, sadly. The optimizer gets serious brain-fade
sometimes. I _really_ want Ann (or anyone!) to explain why it is
that when asked to select A and B from ABC or ADEF, the optimizer
chooses the latter. Does it just choose the index with the greater
number of fields? This just seems crazy, but I suppose there must be
logic behind it or it wouldn't be programmed this way.

But it's like having two baskets. Basket A has apples, oranges, and
grapes. Basket B contains apples, pears, bananas, and mango. Ask a
child to go choose the one basket where they can get both an apple
and an orange... a two-year-old could do it but apparently the
optimizer can't! Go figure. :-)

Regards, Andrew Ferguson