1 / 7

Index Example

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

denna
Download Presentation

Index Example

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Index Example From Garcia-Molina, Ullman, and Widom: Database Systems, the Complete Book pp. 298 - 300

  2. 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.

  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

  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) ε 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.

More Related