Svein,

Indeed - rdb$foreign76 is based on SYS_FILIA_ID, TOWAR_ID, STAN_ID
and the 2=0 trick worked - 3,5 times faster than concatenation!
I've spent quite a few years on designing databases but haven't
learned about that one. Is it a generic rule for any SQL engine or
something characteristic for IB/FB?

Also - let me understand what mechanism is used here:

Does DB engine look at EACH of the condition parts (AND...OR...) and
looks for ANY indexes (PK, FK, IX) containing ANY of the fields used
in the given condition part?

What in fact is enforced by 'or-ing' one of the condition parts?

Thanks,
Tomasz

--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@k...> wrote:

> Occationally Firebird/InterBase chooses to use too many indexes.

Even

> though you are using the primary key in your plan, it thinks that

it would

> be even better if it added another and rdb$foreign76 is an index

covering

> either SYS_FILIA_ID or KOMPLET_ID or both. To improve speed in such

cases,

> one often have to prevent indexes from being used - either doing

what you

> have done or 'or it' with something evaluating to false:
>
> SELECT KT.*
> FROM KOMPLET_TOWAR KT
> JOIN KOMPLET K
> ON KT.SYS_FILIA_ID = K.SYS_FILIA_ID AND KT.KOMPLET_ID = K.KOMPLET_ID
> LEFT OUTER JOIN TOWAR T
> ON KT.SYS_FILIA_ID = T.SYS_FILIA_ID AND KT.TOWAR_ID = T.TOWAR_ID
> LEFT OUTER JOIN TOWAR_TYP TT
> ON T.TOWAR_TYP_ID = TT.TOWAR_TYP_ID
> WHERE
> (KT.SYS_FILIA_ID = 1 or 2=0) and KT.KOMPLET_ID = 112
>

<CUT>