Hi Carsten,

> > 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))

Now for both tables indexes are used and that seems to be much faster.

> 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 ?

Good question (may be wrong selectivity in index statistics), but the JOIN order
has been changed and that is most effective result at the moment.

> Do I have to create compound indexes on every combination of or-clauses ?

No, only when a compound index is usefull (all fields are used for filtering).

> 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.

If you remove the compound index, the query below should give the same PLAN.

SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
WHERE
F_ID_WERKSTOFF = 11 OR
F_ID_WERKSTOFF = 245

In this query i force the optimizer to ignore f_id_auftrag to be used in a
index, thus it will put t_apos at the first position in the join order.

Could you run SET STATISTICS INDEX <name> for every index on t_apos and
t_auftrag to be sure those are correct and then try also again with your
original query.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info