Database Basics
A B+ tree database index was created on the member_no column of a member table with 1 million rows. Which correctly pairs the effect that can generally be expected with its cost?
- ASearches such as WHERE member_no = 12345 become faster, but writes become slower because the index must also be updated every time a row is added or updated
- BSearches on every column become faster, at no cost
- CThe table's rows are sorted by member_no, so every search becomes faster, and the storage needed is halved
Show answer and explanation
Answer: A. Searches such as WHERE member_no = 12345 become faster, but writes become slower because the index must also be updated every time a row is added or updated
A database index is a data structure for quickly finding, from a value in a particular column, the location of the rows holding that value; relational databases commonly use B+ trees. Without an index, finding the row WHERE member_no = 12345 requires examining 1 million rows in order (a full table scan), but with a B+ tree the row is found by following just a few levels from the root to a leaf. If each node branches 100 ways, for example, 100 × 100 × 100 = 1 million, so about three levels are enough. Because the leaves of a B+ tree are linked in value order, the index can also be used for range searches such as BETWEEN and for sorting with ORDER BY. On the other hand, the index must be updated each time a row is inserted, updated or deleted, so writes become slower, and storage is needed for the index. Searches on columns without an index do not become faster.
- A. Correct. Searches become faster, but maintaining the index adds work to every write.
- B. An index helps searches on the column it was created on. It also has costs: extra write work and storage.
- C. An index is a data structure created separately from the table, and it increases storage rather than reducing it.