Index Example

1
Index Example

• From Garcia-Molina, Ullman, and Widom
• Database Systems, the Complete Book
• pp. 298 - 300

2
What is an Index?

• Lets 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.

3
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

4
Example from textbook

• StarsIn(movieTitle, movieYear, StarName)
• Query 1 (Q1) SELECT movieTitle, movieYear
• FROM StarsIn
• WHERE starName s
• Query 2 (Q2) SELECT starName
• FROM StarsIn
• WHERE movieTitle t
  AND movieYear y
• Insertion (I) INSERT INTO StarsIn
• SET StarName
  s, movieTitle t, movieYear y
• Assumptions on average, each star has appeared
  in 3 movies, and each movie has 3 stars table
  takes up 10 disk blocks

5
Cost of Queries
Action No index Star index Movie Index Both indices
Q1 10 4 10 4
Q2 10 10 4 4
I 2 4 4 6
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

7
Implementation

• An index can be defined on multiple attributes A,
  B. In this case the domain is the set of ordered
  pairs (a, b) e 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.