I really need some help in optimizing the following query. It currently takes over 17 seconds. This is very frustrating since the response table represents a miniscule fraction of the amount of data that will eventually be there.
SELECT distinct response.*, question.question_id
FROM response, response r2, response r3, question, page
WHERE question.page_id = page.page_id
and page.survey_id = 350630
and response.question_id = question.question_id
AND ((response.user_id = r2.user_id and r2.question_id = 4117432) OR (response.user_id = r3.user_id and r3.question_id = 4117951 and r3.key1 = 38334668))
This query loads all responses from a survey if the reviewer answered question X or answered question Y with a certain choice.
Please see the attached jpeg for the output of the EXPLAIN.
SELECT distinct response.* FROM response, response r2, question, page WHERE question.page_id = page.page_id and page.survey_id = 350630 and response.question_id = question.question_id AND (response.user_id = r2.user_id and (r2.question_id = 4117432 or (r2.question_id = 4117951 and r2.key1=38334668)));
I'm not sure exactly why it was looking at so many rows before (especially in the former r3) but this query takes 0.1 seconds now. This adds difficulty to change this from the programming side, but I'm grateful for the speed increase.
Thanks to all those who took time to look at this post