"Arno Brinkman" wrote:

> Hi,
>
>
> The optimizer chooses indexes based on selectivity. For a OR
> condition every comparison inside that should be able to use a index
> or none index (for those comparisons inside the OR) is used at all. I
> guess your problem here is the foreign-key on F_ID_WERKSTOFF, because
> this one has many duplicates?
> If so the optimizer still doesn't see it as bad enough to ignore.
>
> One of the things you can do is changing the comparison, such as :
>
> SELECT
> t_apos.ID_APOS
> FROM
> t_apos
> JOIN t_auftrag ON f_id_auftrag = id_auftrag
> WHERE
> F_ID_WERKSTOFF + 0 = 11 OR
> F_ID_WERKSTOFF + 0 = 245
>

This one is a faster (about 10 sec), but not fast enough (PLAN JOIN (T_APOS
NATURAL,T_AUFTRAG INDEX (RDB$PRIMARY39)))

> Another option is if f_id_auftrag and f_id_werkstoff are together
> very selective and create a compound index on (f_id_auftrag,
> F_ID_WERKSTOFF). But to let the optimizer choose the correct compound
> index you should change your query to :
>
> SELECT
> t_apos.ID_APOS
> FROM
> t_auftrag
> JOIN t_apos ON (
> (f_id_auftrag = id_auftrag and F_ID_WERKSTOFF = 11) OR
> (f_id_auftrag = id_auftrag and F_ID_WERKSTOFF = 245))
>

I created this coumpond index:
create index c_auftrag_werktoff on t_apos (f_id_auftrag, F_ID_WERKSTOFF)
Now this one is slower than your first one but faster than my query (about
12 sec)
(PLAN JOIN (T_AUFTRAG NATURAL,T_APOS INDEX
(C_AUFTRAG_WERKTOFF,C_AUFTRAG_WERKTOFF)))

But after i create the compound index my original slow query is fast
(<10ms).
PLAN JOIN (T_APOS INDEX (RDB$FOREIGN161,RDB$FOREIGN161),T_AUFTRAG INDEX
(RDB$PRIMARY39))
although the compound index is not used Firebird now uses a index for the
join and not a natural join.
Whats your explanation for this behaviour ?
Do I have to create compound indexes on every combination of or-clauses ?
My problem is that the user can construct this select and it can consist of
much more joins with other tables and can be much more complex (abitrary
composition of 'or' and 'and' clauses).
So Its impossible to create indexes for all possibilities.

mfg
Carsten