Subject Re: Firebird 2.1.3 optimizer in does not use index - Email found in subject
Author Andreas Rulle
Hello Ann,

thank you for your very clear explanation.

We have to admit that the constructed example is over simplified. The productive statement, that is partially generated, is the following:

SELECT DISTINCT Artikel.nNummer FROM Artikel LEFT JOIN AuditsHorizontal
ON AuditsHorizontal.sKey = Artikel.nNummer AND (AuditsHorizontal.sScheme = 'Vorgabe_PBSeasyXML') AND (AuditsHorizontal.sTLC = 'Artikel')
(( Artikel.nSortiment = 402087401 )
AND ( Artikel.cSprache = 'deu' )
AND ( Artikel.bGeloescht = 0 )
AND ((( AuditsHorizontal.dtModified <= Artikel.dtGeaendert ))
OR (( AuditsHorizontal.sScheme IS NULL )
AND ( AuditsHorizontal.sTLC IS NULL ) AND ( AuditsHorizontal.dtModified IS NULL ))
OR ( AuditsHorizontal.sAuditVersion != '01.00.003' )

And, as it can be seen, it has a where clause and, as it has been explained on this great list (!), therefore uses an index on the left table Artikel (= Article).

The plan, that is used, is


And therefore Firebird 2.1.3 answers this query in 0.015 s for about 55000 rows in both tables, if it does not return an Article

We started from an exexution time of more than a hour and that makes optimizing a query - with the qualified help of all that have contributed to this on this list - an inspiring task. Thank you all!