Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2005
    Posts
    4

    Unanswered: Complicated Query Running VERY Slow

    I have a series of related tables and I'm trying to use a LEFT JOIN to pull it all together into a report.

    Here's my query:

    SELECT Aircraft.name as aircraft_name, Questions.question as question, Responses.answer as answer
    FROM Responses
    LEFT JOIN Events
    ON Responses.category1 = Events.id
    LEFT JOIN Classes
    ON Events.class_id = Classes.id
    LEFT JOIN Aircraft
    ON Classes.aircraft_id = Aircraft.id
    LEFT JOIN Questions
    ON Responses.question_id = Questions.id
    WHERE Aircraft.Type = 'Fighter'
    AND Questions.type = 'Measure'
    ORDER BY Questions.sortorder
    LIMIT 0,50

    Responses is a 500,000 record table. The other tables are relatively small (1-500 records). I've confirmed that all of the table's keys are indexed as either unique (primary keys) or normal (foreign keys). An EXPLAIN query returns this:

    Responses ALL 523039 Using temporary; Using filesort
    Events eq_ref PRIMARY,id PRIMARY 4 Responses.category1 1
    Classes eq_ref PRIMARY,id PRIMARY 4 Events.class_id 1
    Aircraft eq_ref PRIMARY,id PRIMARY 4 Classes.aircraft_id 1 where used
    Questions eq_ref PRIMARY,id PRIMARY 4 Responses.question_id 1 where used

    Which, I think, means that the query is scanning all 500,000 records in the Responses table against the indexes in the related tables. I've done similar joins in the past, but never against sets of data this large. I would have expected my WHERE clause to have limited the number of responses significantly, but as a precaution, I added the LIMIT statement to ensure that the bottleneck was actually in the query analysis. Am I doing something wrong, or is this type of join just impossible in this situation?

    I really appreciate any help or direction anyone can provide. I'm stuck.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you say all of the keys are "indexed as either unique (primary keys) or normal (foreign keys)"

    could you please script the tables and confirm what indexes exist?

    also, the fact that you have WHERE conditions on the Aircraft and Questions tables means that the joins involving those tables will actually be inner joins, not left outer

    those conditions should be moved to the ON clauses

    this will not affect performance, but it will change which rows you get back, if in fact you do really want a left outer join
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2005
    Posts
    4
    Thanks for the response. Here are my table definitions.

    Code:
    mysql> show fields from responses;
    +---------------+-----------------+------+-----+---------+----------------+
    | Field         | Type            | Null | Key | Default | Extra          |
    +---------------+-----------------+------+-----+---------+----------------+
    | id            | int(9) unsigned |      | PRI | NULL    | auto_increment |
    | index_id      | int(9)          | YES  | MUL | NULL    |                |
    | survey_id     | int(9)          | YES  | MUL | NULL    |                |
    | question_id   | int(9)          | YES  | MUL | NULL    |                |
    | question_text | text            | YES  |     | NULL    |                |
    | answer        | text            | YES  |     | NULL    |                |
    | creator_id    | varchar(255)    | YES  |     | NULL    |                |
    | date_created  | date            | YES  |     | NULL    |                |
    | time_created  | time            | YES  |     | NULL    |                |
    | categoryName1 | varchar(255)    | YES  |     | NULL    |                |
    | category1     | int(32)         | YES  | MUL | NULL    |                |
    | categoryName2 | varchar(255)    | YES  |     | NULL    |                |
    | category2     | varchar(32)     | YES  |     | NULL    |                |
    | categoryName3 | varchar(255)    | YES  |     | NULL    |                |
    | category3     | text            | YES  |     | NULL    |                |
    | sortorder     | int(11)         | YES  | MUL | NULL    |                |
    | naFlag        | int(11)         | YES  |     | NULL    |                |
    +---------------+-----------------+------+-----+---------+----------------+
    17 rows in set (0.01 sec)
    
    mysql> show fields from events;
    +----------------------+-----------------+------+-----+---------+----------------+
    | Field                | Type            | Null | Key | Default | Extra          |
    +----------------------+-----------------+------+-----+---------+----------------+
    | id                   | int(9) unsigned |      | PRI | NULL    | auto_increment |
    | class_id             | int(9) unsigned | YES  | MUL | NULL    |                |
    | class_name           | text            | YES  |     | NULL    |                |
    | teach_date           | date            | YES  | MUL | NULL    |                |
    | teach_time           | time            | YES  | MUL | NULL    |                |
    | instructor_id        | int(9) unsigned | YES  | MUL | NULL    |                |
    | alt_instructor_id    | int(11)         | YES  | MUL | NULL    |                |
    | location_id          | int(9) unsigned | YES  | MUL | NULL    |                |
    | scheduled_attendance | int(9)          | YES  |     | NULL    |                |
    | actual_attendance    | int(9)          | YES  |     | NULL    |                |
    | cancel_flag          | int(11)         | YES  |     | 0       |                |
    | cancel_reason        | text            | YES  |     | NULL    |                |
    | no_show_flag         | int(11)         |      |     | 0       |                |
    | event_notes          | text            | YES  |     | NULL    |                |
    | created              | datetime        | YES  |     | NULL    |                |
    | creator_id           | int(9) unsigned | YES  |     | NULL    |                |
    +----------------------+-----------------+------+-----+---------+----------------+
    16 rows in set (0.01 sec)
    
    mysql> show fields from classes;
    +-------------------+-----------------+------+-----+---------+----------------+
    | Field             | Type            | Null | Key | Default | Extra          |
    +-------------------+-----------------+------+-----+---------+----------------+
    | id                | int(9) unsigned |      | PRI | NULL    | auto_increment |
    | name              | varchar(255)    | YES  |     | NULL    |                |
    | description       | text            | YES  |     | NULL    |                |
    | type              | varchar(255)    | YES  |     | NULL    |                |
    | aircraft_id       | int(9) unsigned | YES  | MUL | NULL    |                |
    | location_id       | int(9) unsigned | YES  | MUL | NULL    |                |
    | instructor_id     | int(9) unsigned | YES  | MUL | NULL    |                |
    | alt_instructor_id | int(11)         | YES  | MUL | NULL    |                |
    | total_required    | int(9) unsigned | YES  |     | NULL    |                |
    | created           | datetime        | YES  |     | NULL    |                |
    | creator_id        | int(9) unsigned | YES  |     | NULL    |                |
    | survey_id         | int(11)         |      |     | 0       |                |
    +-------------------+-----------------+------+-----+---------+----------------+
    12 rows in set (0.01 sec)
    
    mysql> show fields from aircraft;
    +--------------+--------------+------+-----+---------+----------------+
    | Field        | Type         | Null | Key | Default | Extra          |
    +--------------+--------------+------+-----+---------+----------------+
    | id           | int(9)       |      | PRI | NULL    | auto_increment |
    | name         | varchar(100) | YES  |     | NULL    |                |
    | type         | varchar(100) | YES  |     | NULL    |                |
    | picture_url  | text         | YES  |     | NULL    |                |
    | lead_id      | int(9)       | YES  | MUL | NULL    |                |
    | date_created | datetime     | YES  |     | NULL    |                |
    | date_updated | datetime     | YES  |     | NULL    |                |
    +--------------+--------------+------+-----+---------+----------------+
    7 rows in set (0.00 sec)
    
    mysql> show fields from questions;
    +---------------+-----------------+------+-----+---------+----------------+
    | Field         | Type            | Null | Key | Default | Extra          |
    +---------------+-----------------+------+-----+---------+----------------+
    | id            | int(9) unsigned |      | PRI | NULL    | auto_increment |
    | survey_id     | int(9) unsigned | YES  | MUL | NULL    |                |
    | section_id    | int(9) unsigned | YES  | MUL | NULL    |                |
    | question      | text            | YES  |     | NULL    |                |
    | type          | varchar(32)     | YES  | MUL | NULL    |                |
    | default_value | text            | YES  |     | NULL    |                |
    | length        | int(11)         | YES  |     | NULL    |                |
    | option1       | varchar(50)     | YES  |     | NULL    |                |
    | option2       | varchar(50)     | YES  |     | NULL    |                |
    | option3       | varchar(50)     | YES  |     | NULL    |                |
    | option4       | varchar(50)     | YES  |     | NULL    |                |
    | option5       | varchar(50)     | YES  |     | NULL    |                |
    | naFlag        | int(11)         | YES  |     | NULL    |                |
    | sortorder     | int(11)         | YES  | MUL | NULL    |                |
    +---------------+-----------------+------+-----+---------+----------------+
    14 rows in set (0.02 sec)

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what does MUL mean?

    can you script the CREATE INDEX statements?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2005
    Posts
    4
    That just means that it's indexed and that multiple items with the same key can exist (which is correct since these are the foreign keys). From the MySQL Manual:

    The Key column indicates whether the field is indexed. A value of PRI indicates that the field is part of the table's primary key. UNI indicates that the field is part of a UNIQUE index. The MUL value indicates that multiple occurences of a given value allowed within the field.

    bpopp

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, if all collumns used to join tables or in the WHERE clause have indexes, then you need a different approach

    why again did you want a left join for questions?

    can there be responses with no matching question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2005
    Posts
    4

    Bless you

    Bless you! That was it. I replaced the LEFT JOIN with an INNER JOIN:

    SELECT Aircraft.name as aircraft_name, Questions.question as question, Responses.answer as answer
    FROM Responses
    LEFT JOIN Events
    ON Responses.category1 = Events.id
    LEFT JOIN Classes
    ON Events.class_id = Classes.id
    LEFT JOIN Aircraft
    ON Classes.aircraft_id = Aircraft.id
    INNER JOIN Questions
    ON Responses.question_id = Questions.id
    WHERE Aircraft.Type = 'Fighter'
    AND Questions.type = 'Measure'
    ORDER BY Questions.sortorder
    LIMIT 0,50

    And now an examine looks like:

    Questions range PRIMARY,id,type type 33 666 where used; Using filesort
    Responses ref question_id_idx question_id_idx 5 Questions.id 785 where used
    Events eq_ref PRIMARY,id PRIMARY 4 Responses.category1 1
    Classes eq_ref PRIMARY,id PRIMARY 4 Events.class_id 1
    Aircraft eq_ref PRIMARY,id PRIMARY 4 Classes.aircraft_id 1 where used

    Now the query returns very quickly (and appears to be correct). Thanks so much for the help!
    Last edited by bpopp; 02-03-05 at 13:23.

Posting Permissions

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