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

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database development support:
http://www.databasedevelopmentforum.com

Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Firebird and Interbase users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info