Database Indexing hard
What is the benefit of `CREATE INDEX (A) INCLUDE (B)` vs `CREATE INDEX (A, B)`?
- A The `INCLUDE` column is only stored in the leaf nodes, keeping the index's internal branch nodes lean and the overall tree height shallow.
- B It utilizes significantly less physical disk space than a standard composite index by applying advanced delta-compression to the non-key columns.
- C All columns listed in the `INCLUDE` clause are automatically encrypted at the storage level to ensure a high level of data privacy for sensitive fields.
- D The `INCLUDE` column is stored in Every node of the B-Tree, ensuring that point-lookups can resolve the value without visiting the leaf nodes.
Answer: A. The `INCLUDE` column is only stored in the leaf nodes, keeping the index's internal branch nodes lean and the overall tree height shallow.
A composite index `(A, B)` stores both A and B in every node of the B-Tree. If B is a large field (like a long string), this 'bloats' the index, making the tree deeper and less cache-friendly. `INDEX (A) INCLUDE (B)` only uses A for the actual tree structure. B is 'carried along' as extra data at the very bottom (the leaves). This keeps the index small and fast while still enabling 'Index-Only Scans' for queries that need B, provided they are filtered by A.
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')?