full code review

Is there are sense of how many rows there are per pid1 values, per (pid1, qid1) tuples, and per (pid1, qid1, pid2) tuples. How often is qid1 going to be null? Also, what are the variations that context might have? The current secondary index looks plausible for the WHERE, though it's hard to say for sure without knowing the selectivity.

What range of values might $limit have? This query will need a quicksort, though that's OK as long as the number of matching rows from the WHERE is moderate. Likewise the HAVING can't use any index, though it's ok if the matching rows from the WHERE are moderate. Is this likely to be the case?