--- In firebird-support@yahoogroups.com, Carsten Schäfer wrote:

> The following query is slow
> SELECT t_apos.ID_APOS
> FROM t_apos
> JOIN t_auftrag ON f_id_auftrag = id_auftrag
> WHERE f_id_werkstoff = 10
> or t_auftrag.f_adatum between '31.10.2004 00:00' AND '02.12.2004
> 11:23'
> PLAN JOIN (T_AUFTRAG NATURAL,T_APOS INDEX (RDB$FOREIGN1))
>
> 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.

I suspect doing an addition of the two clauses would be more like

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

Firebird attempts to find one optimal plan, what you seem to desire is
two separate plans with the result joined together. In many cases that
would be sub-optimal.

That being said, I do think Firebird is better at handling INNER JOIN
and AND than OUTER JOIN and OR, just like I am better at solving one
problem at a time than several (I can wash my hands or write programs,
doing both at the same time ruins my computer!). It can be extremely
difficult to unleash the full power of Firebird to users, and at the
same time prevent them from suffering the pitfalls. Firebird will
inevitably punish you for issuing an all-too-demanding query.

Set