Hi, I have a query that is taking quite a bit of time to compute and
was wondering if anyone has any pointers on how to improve on this.

The query itself is to return the number of responses from the
databse from a question, but only from participants selecting a
particular response of another question. I have read on here counts
are expensive and so are INs, so using both in the same query is
probably bad.

The query

select count(userresponse) as numberofanswers
from response r
where r.userresponse = ?CurrentResponse and
r.surveyparticipant in (select r2.surveyparticipant
from response r2
inner join question q on (q.questionid = r2.questionid and
r2.userresponse = ?Responseid and
q.questionid = ?dropdownquestionid
)
)

Plan
PLAN JOIN (Q INDEX (RDB$PRIMARY4),R2 INDEX (RDB$FOREIGN28))
PLAN (R INDEX (RDB$FOREIGN28))

Adapted Plan
PLAN JOIN (Q INDEX (PK_QUESTION),R2 INDEX (FK_RESPONSE_POSRESPONSE))
PLAN (R INDEX (FK_RESPONSE_POSRESPONSE))


I have tested against interbase 6 and firebird 1.5. The above plan
was created against firebird.

any pointers would be gratefully accepted.

Alan