Complicated Query Running VERY Slow

bpopp · #1 (**permalink**) 02-02-05, 18:56

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.

r937 · #2 (**permalink**) 02-02-05, 20:56

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

bpopp · #3 (**permalink**) 02-03-05, 00:21

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)

r937 · #4 (**permalink**) 02-03-05, 06:36

what does MUL mean?

can you script the CREATE INDEX statements?

bpopp · #5 (**permalink**) 02-03-05, 11:19

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

r937 · #6 (**permalink**) 02-03-05, 11:26

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?

bpopp · #7 (**permalink**) 02-03-05, 12:17

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!

Internet Services

Web Development

Digital Marketing

Consumer Tech