What's the best way to find common rows in a table

We have a web project, there are some multiple-choice questions and the registered users answer them...

I want to find the common answers of two users to give a comparison. I mean, the logged in user will go to some other user's profile page and see the comparison of their answers for their common questions only...

The tables are like (simplified):

questions

id

question

active -> enum('Y', 'N')

answers

id

question_id

answer

users

id

nick

user_answers

user_id

question_id

answer_id

private -> enum('Y', 'N')

I can join user_answers table with itself by giving an alias but I have to join other tables too. Private answers should not be taken into account, only active questions should be taken...

The site is expected to get some load so I'm afraid there are too many joins and where conditions. From my experiences I know that these complex queries can lock the tables and cause some serious performance issues, especially under heavy load...

So what would be the best practice when scalability and performance is taken into account... Would smt. like sphinx or solar help, or any software based solution to do the comparison?

The results will be paginated by t开发者_JAVA百科he way...

Right now I'm thinking of seperating question details and answer details and cache them, so the query will be smt. like:

select ua1.answer_id as her_answer_id,
       ua2.answer_id as my_answer_id,
       ua1.question_id
  from user_answers ua1
 inner join users_answers ua2 on ua1.question_id=ua2.question_id
 where ua1.user_id=$herId
   and ua2.user_id=$myId
   and ua1.private='N'
 order by ua1.question_id desc

questions.question and answers.answer will be taken from cache... In this case passive questions will be a problem but I think I'll try to move the passive questions yo some backup database which will complicate things...

I would use a conditional in the query:

select
user_answers.question_id `QuestionId`,
max(if((user_answers.user_id = my_id), user_answers.answer_id, 0)) `MyAnswer`,
max(if((user_answers.user_id = other_id), user_answers.answer_id, 0)) `OtherAnswer`
from users_answers
where user_answers.private = 'N'
and user_answers.user_id IN(myid, orderid)
group by users_answers.question_id
having count(*) = 2
order by user_answers.question_id

Haven't tested it, but you should get the idea!

Hope this works out for you...