Database Indexing easy
You have a composite index on `(Lastname, Firstname)`. Which query can efficiently use this index?
- A A query filtering by the second column only: `WHERE Firstname = 'Alice'`
- B A query filtering by the leading column only: `WHERE Lastname = 'Smith'`
- C A query filtering by an unrelated third column: `WHERE Age > 25`
- D A query that combines the second column with an external range filter: `WHERE Firstname = 'Alice' AND Age > 25`
Answer: B. A query filtering by the leading column only: `WHERE Lastname = 'Smith'`
Composite (multi-column) indexes are stored in a strict left-to-right sort order. Think of an old-fashioned phone book: it is sorted by `(Lastname, Firstname)`. You can easily find 'Smith', or 'Smith, Alice', but you cannot find 'Alice' without reading the whole book from start to finish. This is known as the 'Leftmost Prefix Rule', and it is essential for designing indexes that are reusable across multiple different query patterns.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?