Index Example From GarciaMolina Ullman and Widom Database
Index Example From Garcia-Molina, Ullman, and Widom: Database Systems, the Complete Book pp. 298 - 300
What is an Index? • Let’s say relation R has an attribute A • An index on A is a data structure that allows quick access to tuples of R if you know the value of A • Implementation: hash table or similar data structure.
Indices and database design • Important fact: disk accesses are typically the highest cost operation for a DBMS • Having an index on A speeds up database lookups involving A • However, it slows down insertions and deletions involving A, because the index must also be updated
Example from textbook Stars. In(movie. Title, movie. Year, Star. Name) • Query 1 (Q 1): SELECT movie. Title, movie. Year FROM Stars. In WHERE star. Name= s • Query 2 (Q 2): SELECT star. Name FROM Stars. In WHERE movie. Title= t AND movie. Year= y • Insertion (I): INSERT INTO Stars. In SET Star. Name= s, movie. Title= t, movie. Year= y Assumptions: on average, each star has appeared in 3 movies, and each movie has 3 stars; table takes up 10 disk blocks
Cost of Queries Action No index Star index Movie Index Both indices Q 1 10 4 Q 2 10 10 4 4 I 2 4 4 6
Conclusions: • If lookups on an attribute A are much more common than insertions and deletions, it makes sense to add an index on A • But if lookups are not common, the index may slow down database performance
Implementation • An index can be defined on multiple attributes A, B. In this case the domain is the set of ordered pairs (a, b) ε A x B • Some DBMS implementers automatically add an index to each primary key attribute. • This is useful because any insertion to a table with a key requires a lookup to ensure that the key remains unique.
- Slides: 7