(replying to both firebird-support and Tom since 4 messages within the
last 30 hours to me indicates that something is still troubling
firebird-support@yahoogroups).

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

A AND C1 AND C2

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

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

HTH,
Set

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