| Subject | Re: [firebird-support] Question about index use |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2019-03-07T08:27:37Z |
Sure, the results of
Select * from table_A a left join table_B b on a.field_A = b.field_A where b.field_B = 1;
and
Select * from table_A a inner join table_B b on a.field_A = b.field_A where b.field_B = 1;
are identical. However, in the first case you're telling Firebird to explicitly do a LEFT JOIN, something which makes Firebird choose to have table_A before table_B in the plan (it does expect LEFT to be a deliberate choice you've made and not that you originally wrote left and then changed your mind in the WHERE clause. Using LEFT rather than INNER join is a possible way (although unusual) to optimize slow queries). Basically, with left join, the three plans available to Firebird (besides NATURAL, NATURAL) are (forgive me for not getting the surrounding PLAN JOIN, PLAN ORDER, INDEX, parenthesis and commas correct) are:
(table_A NATURAL, table_B (table_B_field_B_index, table_B_field_A_index)), (table_A NATURAL, table_B (table_B_field_B_index)) or (table_A NATURAL, table_B (table_B_field_A_index))
(notice that it has to go natural on table_A in all three cases since there's no fixed value available) whereas the inner join also makes it possible for the optimizer to choose which table to put first in the plan, something which also makes it possible to choose
(table_B (table_B_field_B_index), table_A(table_A_field_A_index))
Normally (not always), this latter index would be the one to prefer.
HTH,
Set
Den ons. 6. mar. 2019 kl. 19:41 skrev Carsten Schäfer ca_schaefer@... [firebird-support] <firebird-support@yahoogroups.com>:
Hi,
I'm using FB 3.0.4 on Windows and i'm asking why the index (on field_B)is not used in the follwing query?
Select * from table_A a left join table_B b on a.field_A = b.field_A where b.field_B = 1;
field_A is a standard foreign key and field_B is an indexed field but the index is not used and so the query is slow (for big tabels).
When i use an inner join the index on field_B is used, but i don't get why FB has to make a difference in this case.
In this case the result set should always be the same, regardless of inner join or left join, or not?
Regards
Carsten