1

I have a 'students' table, a 'classes' table and I have a junction table that tells which students have which classes. I would like to get a list of the classes a certain student has. And I want to order that list by the name of the classes.

My problem is that MySQL creates a temporary table when it executes my query. And I think this is the reason that this query runs much slower than the same query without the 'order by' clause.

In reality on our server our tables have different names and our junction table has more than 10 million rows. And the query returns thousands of rows. A large portion of the load of our server comes from that single query.

SELECT classes.* FROM classes
INNER JOIN students_classes
ON classes.id = students_classes.class_id
WHERE students_classes.student_id = 2
ORDER BY classes.name

MySQL explain gives the following output:

'id' => 1, 'select_type' => 'SIMPLE', 'table' => 'students_classes', 'type' => 'ref', 'possible_keys' => 'PRIMARY,student,class', 'key' => 'student', 'key_len' => '4', 'ref' => 'const', 'rows' => 2, 'Extra' => 'Using index; Using temporary; Using filesort'

'id' => 1, 'select_type' => 'SIMPLE', 'table' => 'classes', 'type' => 'eq_ref', 'possible_keys' => 'PRIMARY', 'key' => 'PRIMARY', 'key_len' => '4', 'ref' => 'students_classes.class_id', 'rows' => 1, 'Extra' => NULL,

If I remove the 'ORDER BY' clause, mySQL doesn't create a temporary table.

CREATE TABLE `classes` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`),
INDEX `name` (`name`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT
AUTO_INCREMENT=506374;

CREATE TABLE `students_classes` (
`student_id` INT(11) NOT NULL,
`class_id` INT(11) NOT NULL,
PRIMARY KEY (`student_id`, `class_id`),
INDEX `student` (`student_id`),
INDEX `class` (`class_id`),
CONSTRAINT `class_id` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`),
CONSTRAINT `student_id` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

I did not include the create code of the students table because that table doesn't play any role in the query.

MySQL version is 5.5.35.

How can I make that query run faster?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
bala529
  • 11
  • 2
  • How many rows do you get with that query? – Paul Spiegel Mar 16 '17 at 18:16
  • `INDEX student (student_id)` - Drop that index. It's redundant with the primary key. You should also drop `INDEX class (class_id)` and create `INDEX class_student (class_id, student_id)`. Not for this query - But you will probably need it. – Paul Spiegel Mar 16 '17 at 18:24
  • Are the numbers correct in your `explain`? MySQL assumes it will find 2 rows in `students_classes`, and in that case this execution path is really the only choice. If you add Pauls index `students_classes (class_id, student_id)`, and force this index, you can try if preventing the filesort/temptable is faster. There will be a magic number of rows in your result when this way is faster, it depends on "number of results"/"number of total classes". And if all your students have a similar amount of classes. With 1k results, I guess for <10k total classes it might be faster. So try it. – Solarflare Mar 16 '17 at 19:02
  • I got two rows with the query. Thanks for the suggestions, I will experiment. – bala529 Mar 16 '17 at 19:20

0 Answers0