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?
-
Run an explain and mysql will tell you.P.Salmon– P.Salmon2021-02-09 08:35:16 +00:00Commented Feb 9, 2021 at 8:35
-
What would the answer that would satisfy you look like?Strawberry– Strawberry2021-02-09 08:35:54 +00:00Commented Feb 9, 2021 at 8:35
-
You can use EXPLAIN to see the estimated execution plan, and EXPLAIN ANALYZE to see the actual execution plan.Kurt Kline– Kurt Kline2021-02-09 08:36:36 +00:00Commented Feb 9, 2021 at 8:36
-
In what flow query would be executed would join be first or where clause would run first?Sabeeh Zia– Sabeeh Zia2021-02-09 13:24:21 +00:00Commented Feb 9, 2021 at 13:24
-
@P.Salmon - EXPLAIN will tell you what it is doing, but not what could be changed to improve it.Rick James– Rick James2021-02-26 16:49:33 +00:00Commented Feb 26, 2021 at 16:49
Add a comment
|
1 Answer
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.
2 Comments
Sabeeh Zia
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.
Rick James
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: mysql.rjweb.org/doc.php/index_cookbook_mysql