Subject Re: [firebird-support] Postscript to (No longer) the red brick wall
Author Svein Erling Tysvær
(replying to both firebird-support and Tom since 4 messages within the
last 30 hours to me indicates that something is still troubling

Hi Tom!

I'll be using the following abbreviations:

A = candidateid=candidate.candidateid
Bx = Attributeid=<xValue>
Cx = Attributeid<><xValue>

As Arno explained when answering my confusion (thanks Arno, I completely
forgot about multifield indexes), the reason for repeating A was that
the index covering both A and B could be used when doing

(A AND B1) OR (A AND B2)

in a way that isn't possible (Firebird < 2) when doing

A AND (B1 OR B2)

Now, an index is never used for nonequality, hence using

(A AND C1) AND (A AND C2) doesn't have any benefit whatsoever over using


Moreover, you replace OR with AND and that in itself changes the rule of
the game (AND is considerably easier than OR for the optimizer to get

In fact, with non-equality, I'd use NOT IN (or in certain cases NOT
EXISTS) myself. There are potentially many more values that are
different than the number that can be equal (in a general way of
thinking), so I'd expect <> to be more time consuming than =.


Tom Conlon wrote:
> VARIATION 2 (<>) - Execution Time: 27s 860ms :(
> -----------------------------------------------
> JOIN candidateAttribute t7 ON=20
> (=20
> (t7.candidateid=3Dcandidate.candidateid AND t7.Attributeid<>256) AND=20
> (t7.candidateid=3Dcandidate.candidateid AND t7.Attributeid<>34) AND=20
> (t7.candidateid=3Dcandidate.candidateid AND t7.Attributeid<>321) AND=20
> (t7.candidateid=3Dcandidate.candidateid AND t7.Attributeid<>3563 )=20
> )=20
> VARIATION 3 (OR + <>) - Execution Time: 39s 468ms :(
> ----------------------------------------------------
> JOIN candidateAttribute t7 ON=20
> (=20
> (t7.candidateid=3Dcandidate.candidateid AND t7.Attributeid=3D321) OR=20
> (t7.candidateid=3Dcandidate.candidateid AND t7.Attributeid=3D3563)=20
> )=20
> JOIN candidateAttribute t8 ON=20
> (=20
> (t8.candidateid=3Dcandidate.candidateid AND t8.Attributeid<>256) AND=20
> (t8.candidateid=3Dcandidate.candidateid AND t8.Attributeid<>34)=20
> )=20
> The plans show all available indexes being used. Maybe a different
> strategy is required for the <> test?
> Tom