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.

Table info:

question
15 rows
question_id - primary key, int(9)
page_id - index, mediumint(8)

page
1 row
page_id - primary, mediumint(8)

response
313 rows
response_id - primary, int(9)
user_id - index, int(9)
question_id - index, int(9)

These are all MyISAM tables. Using mysql 4.0.18 on a G4 powerbook 1 MHz, 1 GB RAM.

Thanks for your help!
Paul

Well, I've considerably reduced the query time.

The new query looks like:

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