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?
Asked
Active
Viewed 104 times
0
ysth
- 96,171
- 6
- 121
- 214
Sabeeh Zia
- 13
- 4
-
Run an explain and mysql will tell you. – P.Salmon Feb 09 '21 at 08:35
-
What would the answer that would satisfy you look like? – Strawberry Feb 09 '21 at 08:35
-
You can use EXPLAIN to see the estimated execution plan, and EXPLAIN ANALYZE to see the actual execution plan. – Kurt Kline Feb 09 '21 at 08:36
-
In what flow query would be executed would join be first or where clause would run first? – Sabeeh Zia Feb 09 '21 at 13:24
-
@P.Salmon - EXPLAIN will tell you what it is doing, but not what could be changed to improve it. – Rick James Feb 26 '21 at 16:49
1 Answers
0
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.
Tim Biegeleisen
- 502,043
- 27
- 286
- 360
-
why would student table be smaller than courses table. If this is a university database then definitely student will be greater. But thanks for the tip on creating index on where clause column and foreign key. – Sabeeh Zia Feb 09 '21 at 08:48
-
Furthermore, that index will cover the `ORDER BY`, thereby letting it get to the `LIMIT` -- hence it will touch only 50 rows (and not do a sort). Table size and FK are not relevant here. More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql – Rick James Feb 26 '21 at 16:48