Which one of these queries has a better performance?

Question

I have three tables, with these fields:

classes: class_id | name | grade

classes_students: class_id | student_id

students: student_id | name

Classes has a n:m relationship with Students, so one class can have many students, and one student can listen to many classes. I want to select all students of a particular class, where class_id is 5.

Here are three queries that do the same thing. I run against MySQL latest version, InnoDB engine. Which one would show better performance and why?

Query A)

SELECT s.name
  FROM students s
  JOIN classes_students cs ON cs.student_id = s.student_id AND cs.class_id = 5

Query B)

SELECT s.name
  FROM students s
  JOIN classes_students cs ON cs.student_id = s.student_id
  JOIN classes c ON c.class_id = cs.class_id
 WHERE c.class_id = 5

Query C)

SELECT s.name 
 FROM students s 
 INNER JOIN classes_students cs ON cs.student_id = s.student_id
WHERE cs.class_id = 5

Answer 1

The only true way to know is to try them all, and compare the performance on your environment. Always test queries out on your environment, benchmark them to see what actually performs best as you will never truly know otherwise.

Generally speaking and assuming all indexes are in place, I'd expect B to be worst as it has more JOINs/more tables involved. A and C I'd expect to be the same (probably will be optimised to the same execution plan)

Answer 2

MySQL offers the EXPLAIN statement that will detail you the execution plan.

I did not run it but my bet is that the three queries are processed exactly the same way.

If you really need to tweak MySQL optimizer, you can have a look at the STRAIGHT_JOIN operator but MySQL is generally quite clever.