Indexes: fast lookups at a write cost

Computing · Data & Databases · ages 19-20

Name ______________________ Date ____________

Why are B-tree nodes wide rather than binary?
- Keys share a read
- Wide nodes use less storage
- Wide nodes skip all write costs
A users table has an index on email and no other index. Which lookup does the index speed up?
- Finding users older than 30
- One email lookup
- Grouping users by country
Which cost does every index add?
- Slower but correct results
- Extra rows in answers
- Write work plus storage
A table with five indexes inserts slowly. What is the likely cause?
- Five index writes per insert
- The disk reads pages too quickly
- Too few queries use the table
The email index stores values in order. Which extra query can use it?
- Counting rows by country
- Averaging all ages
- Email names from a to m
A B-tree index helps only exact matches, never ranges.
Circle one: True False
An index on city helps a query that filters rows by city and sorts by city.
Circle one: True False
Jo wants an index on every column to make all queries fast. What is wrong with the plan?
- Queries would return wrong rows
- Write and storage tax per index
- Two indexes cannot share one table

Answer key

For grown-ups. Fold this page away before handing over the rest.

Keys share a read · A node fills a disk page, and the disk reads a page at a time.
One email lookup · The index covers email only, so only the email lookup can use it.
Write work plus storage · Each write must update every index, and each index occupies space.
Five index writes per insert · Write work grows with each index, so heavy indexing slows inserts.
Email names from a to m · Order serves ranges, so a range over email can use the same index.
False · Its keys stay ordered, so ranges and sorts benefit too.
True · One ordered structure serves both the filter and the sort.
Write and storage tax per index · Each index speeds its own lookups but charges on every write.