CODEWITHSUNDEEP
sqlperformanceindexingdatabase
andrepcg
andrepcg

Reputation: 1331

DBMS using index to increase performance (with example)

select s.name,c.name,r.edition,r.grade
from students s, courses c, registrations r
where s.student_id=r.student_id
and r.course_id=c.course_id
and r.grade > 10
order by 1,2,3;

Creating and index in "grade" column will improve this query performance?

Upvotes: 0

Views: 86

Answers (2)

D Stanley
D Stanley

Reputation: 152566

Creating and index in "grade" column will improve this query performance?

Possibly - it depends on how disperse the values in the grade column are - if 99% (for sake of argument) of the values are > 10, the compiler might decide that it's not worth the trouble of using the index when it's going to have to basically scan the table anyways.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

ALMOST any field being used to filter query results will benefit from indexing, so yes, an index on grade will most likely help.

Moving away from old-style joins won't improve performance but it's a good idea:

SELECT s.name,c.name,r.edition,r.grade
FROM registrations r
JOIN courses c
 ON r.course_id=c.course_id
JOIN students s
 ON s.student_id=r.student_id
WHERE r.grade > 10
ORDER BY s.name,c.name,r.edition

Upvotes: 2

Related Questions