Database Indexing easy
When defining a composite index, which column should typically come first?
- A The column with the smallest physical data type (e.g., Integer) to minimize the footprint of the B-Tree's internal branch nodes.
- B The column used in equality predicates (`=`) that has the highest selectivity (i.e., it filters out the largest number of rows).
- C The column used in broad range predicates (e.g., `>` or `<`) to ensure that the search space is immediately partitioned by time or value.
- D The column that appears first alphabetically to ensure that the index's internal sort order is consistent with the database's metadata.
Answer: B. The column used in equality predicates (`=`) that has the highest selectivity (i.e., it filters out the largest number of rows).
The goal of an index is to reach target records in as few steps as possible. By placing the most selective column first, the database can discard the vast majority of non-matching rows at the very top of the B-Tree. If you place a low-selectivity column (like `Status = 'ACTIVE'`) first, the database must still navigate through massive branches of the tree before it can apply more restrictive filters, leading to more disk I/O and slower queries.
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')?