Hi Tom,

first, if i were you, my index strategy:

CandidateID,AttributeID -- PK
AttributeID,Rating,CandidateID -- Unique index

No FK. CandidateID and Attribute also indexed. use check constraints
and triggers to keep consistency for this two fields.


step 1: get the mondatary rows with minimum reading cost.

Select C1.CandidateID, CP.name
from CandidateAttribute C1

join Candidate CP on ( CP.CandidateId=C1.CandidateID
and CP.LookingJob=1 ... )

join CandidateAttribute C2 on ( C2.CandidateId=C1.CandidateID
and C2.AttributeID=107 and C2.Rating=1)
-- if this candidate also perfect Javaist

join CandidateAttribute C3 on ( C3.CandidateId=C1.CandidateID
and C3.AttributeID=225 and C2.Rating<=2)

join CandidateAttribute C4 on ( C4.CandidateId=C1.CandidateID
and C4.AttributeID in(333,334,418) and C4.Rating<=3)

where C1.AttributeID=101 and C1.rating=1
-- get only perfect C++ programmers using index

This should be run fast because of there is no unneeded row reading.

Regards.
Ali