[RESOLVED] Subquery Issue

[bike5]

I have a set of data I need to return based on another query. I can't figure out another way to do this via sql, the query I wrote just runs forever (I killed the process over 600 seconds). If I run the 2 queries by themselves they take less then a fraction of a section.

SELECT quiz_answers.*
FROM quiz_answers 
WHERE quiz_answers.question_id IN (
	SELECT quiz_questions.id
	FROM quiz 
	INNER JOIN quiz_questions_x_quiz ON (quiz_questions_x_quiz.quiz_id = quiz.id)
	INNER JOIN quiz_questions ON (quiz_questions.id = quiz_questions_x_quiz.question_id)
	WHERE quiz.type = 'plt' GROUP BY quiz_questions.id
)

The inner query returns 1,586 results, the query above should return 4 answer results per question result so I should get back 6,344 results. There are about 44,000 total unfiltered results inside the quiz_answers table. The query syntax is correct but it runs forever it seems and doesn't finish (I am sure it would eventually but it should take as long as it was, killed it manually after 600 seconds). Is there another way I could structure it to run faster? If you need more table structure information let me know. Thanks.

PS: I can't tact on the outer query as another join because of the "group by" and the group by is required because the same questions can be used in multiple quizzes and I need the unique set of questions and answers returned.

[tomhath]

First question I have is whether you have indexes on the table/columns you're joining on.

Also, I'd write the query like this. WHERE...IN is often very inefficient:

SELECT quiz_answers.*
FROM quiz_answers 
WHERE exists (
	SELECT 1
	FROM quiz 
	INNER JOIN quiz_questions_x_quiz
          ON quiz_questions_x_quiz.quiz_id = quiz.id
	INNER JOIN quiz_questions
          ON quiz_questions.id = quiz_questions_x_quiz.question_id
	WHERE quiz.type = 'plt'
	  and quiz_answers.question_id=quiz_questions.id
)

[bike5]

Thank you tomhath, that worked perfectly I have never used exists before but it works really well in this situation. And yes I did have indexes on the joined columns, the query ends up taking 0.0009 sec with your sql changes. 🙂