rme174 wrote:

>
> has anyone an idea, why Firebird 2.1.2 does not use the index in Case 2? Using "LIKE" instead of "STARTING WITH" doesn't change anything. Without the "JOIN Ort" the Index is used.

STARTING WITH is considered being less selective than an equality, so
it's expected to return more rows, thus badly affecting the cost of the
whole join chain {P->S->O} as the leading stream gets too large in terms
of cardinality. The join order {O->S->P} is considered cheaper in this
particular case, even without an index on its leading stream.

> CASE 1: "="
> ===========
>
> SELECT P.ID Person
> FROM Person P
> JOIN Strasse S ON S.ID = P.STRASSE
> JOIN Ort O ON O.ID = S.Ort
> WHERE P.UFirma='GU'
>
> PLAN JOIN (P INDEX (PERSON_UFIRMA), S INDEX (RDB$PRIMARY26), O INDEX (RDB$PRIMARY22))
>
> CASE 2: "STARTING WITH"
> =======================
>
> SELECT P.ID Person
> FROM Person P
> JOIN Strasse S ON S.ID = P.STRASSE
> JOIN Ort O ON O.ID = S.Ort
> WHERE P.UFirma STARTING WITH 'GU'
>
> PLAN JOIN (O NATURAL, S INDEX (RDB$FOREIGN29), P INDEX (PERSON_UFIRMA, RDB$FOREIGN58))

What are the execution stats for both queries? Do you have index
statistics up-to-date?


Dmitry