MySql Query optimizer

Question

If I have a query like this

select * from student 
inner join courses on courses.id = student.course_id
where student.gpa >= 3.0
order by student.gpa
limit 50;

How would Mysql execute this query to optimize the cost?

Answer 1

The following index might help:

CREATE INDEX idx ON student (gpa, course_id);

This index would cover the WHERE clause, allowing MySQL to discard any records with a GPA less than 3.0. In addition, it covers the join.