Hi Arno!

How come this works? Admittedly, I haven't seen any plan, but it just
seems strange to repeat the Candidateid for every Attributeid. Is is
due to using an index on t5.Attributeid and putting this first in the
plan? Would using

JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid
AND t5.Attributeid+0 IN(3916,1402,784,3461,2184))

or (probably better)

JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid+0
AND t5.Attributeid IN(3916,1402,784,3461,2184))

have a similar effect?

I'm curious,
Set

--- In firebird-support@yahoogroups.com, "Arno Brinkman" wrote:
> Hi,
>
> > SELECT t0.candidateid
> <...>
> > WHERE t0.Attributeid =256
> >
> > However, add this line (or a NOT variation) for optional
> > attributes and FB takes over 3minutes with the server working
> > very hard:
> > JOIN CandidateAttribute t5 ON (t5.Candidateid=t0.Candidateid AND
> > t5.Attributeid IN(3916,1402,784,3461,2184))
>
> Have you tried :
>
> SELECT
> t0.candidateid
> FROM
> candidateAttribute t0
> JOIN CandidateAttribute t1 ON (t1.Candidateid=t0.Candidateid
> AND t1.Attributeid=34
> AND T1.RATING<200)
> JOIN CandidateAttribute t2 ON (t2.Candidateid=t0.Candidateid
> AND t2.Attributeid=321 )
> JOIN CandidateAttribute t3 ON (t3.Candidateid=t0.Candidateid
> AND t3.Attributeid=3563 )
> JOIN CandidateAttribute t4 ON (t4.Candidateid=t0.Candidateid
> AND t4.Attributeid=29 )
> JOIN CandidateAttribute t5 ON ((t5.Candidateid=t0.Candidateid
> AND t5.Attributeid = 3916)
> OR (t5.Candidateid=t0.Candidateid
> AND t5.Attributeid = 1402)
> OR (t5.Candidateid=t0.Candidateid
> AND t5.Attributeid = 784)
> OR (t5.Candidateid=t0.Candidateid
> AND t5.Attributeid = 3461)
> OR (t5.Candidateid=t0.Candidateid
> AND t5.Attributeid = 2184))
> WHERE
> t0.Attributeid =256