Results 1 to 2 of 2

Thread: SLOW select

  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Thumbs down Unanswered: SLOW select

    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
    Attached Thumbnails Attached Thumbnails explain.jpg  

  2. #2
    Join Date
    Apr 2004
    Posts
    2

    Re: SLOW select

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •