Hi Carsten,

> >> If i use your trick and do the following query:
> >> SELECT t_apos.ID_APOS
> >> FROM t_apos
> >> JOIN t_auftrag ON f_id_auftrag + 0 = id_auftrag
> >> WHERE t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004
> >> 11:23' PLAN JOIN (T_APOS NATURAL,T_AUFTRAG INDEX (RDB$PRIMARY39))
> >
> > This is a complete different query as the previous message?
>
> This is not complete different, just another field, both have an index.

Just another field is different enough. You can't compare apples and oranges.

> In my first message i posted the table definitions, so theoreticaly you can
> test it.

I hope you understand it's much easier posting with aliasses. Then there's no
need to search in my history to your first message.

> f_id_werkstoff is in t_apos and is a foreign key so it has an index.

I wrote :

> > If f_id_werkstoff is in t_apos and f_adatum in t_auftrag then never a
> > index can be used for the condition (t_apos.f_id_werkstoff = x OR
> > t_auftrag.f_adatum = x), because both tables are not "available" at
> > the same time for evaluation.

> What i don't understand: I have 2 queries, both are very fast (<100ms) ,
> but when i compound them with 'or' the query is not usable anymore (>10sec).
> An or-clause should never take much longer than the addition of the 2
> clauses.

You can't say easily i have 2 queries and when i combine them to 1 they should
at least be the same performance of the single queries performance added
together.

1)

SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON t_apos.f_id_auftrag = t_auftrag.id_auftrag
WHERE
t_apos.f_id_werkstoff = 10

In this query both the index on id_auftrag and f_id_werkstoff can be used, thus
both tables use a index for lookup.

2)
SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON t_apos.f_id_auftrag = t_auftrag.id_auftrag
WHERE
(t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004 11:23')

In this query both the index on f_id_auftrag and f_adatum can be used, thus both
tables use a index for lookup.

3)
SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON t_apos.f_id_auftrag = t_auftrag.id_auftrag
WHERE
(t_apos.f_id_werkstoff = 10) OR
(t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004 11:23')

The where clause can never use a index because not both table t_apos and
t_auftrag can be "active" at the same time for index lookup.

In this case you want a UNION ALL instead of building a new query.

SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON t_apos.f_id_auftrag = t_auftrag.id_auftrag
WHERE
t_apos.f_id_werkstoff = 10
UNION ALL
SELECT
t_apos.ID_APOS
FROM
t_apos
JOIN t_auftrag ON t_apos.f_id_auftrag = t_auftrag.id_auftrag
WHERE
(t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004 11:23')

Remove the ALL from the UNION if you don't want duplicate values in the final
result.

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