Database Indexing easy
What is a 'Covering Index'?
- A A specialized, read-only index that is automatically replicated to every standby node in a distributed cluster.
- B A low-level cryptographic index used strictly for encrypting sensitive PII data at the physical disk level.
- C An index that contains every column requested by a query, allowing the database to skip the physical table lookup entirely.
- D A synonym for the table's Primary Key index, as it inherently covers every row and column within the dataset.
Answer: C. An index that contains every column requested by a query, allowing the database to skip the physical table lookup entirely.
A 'Covering Index' is a powerful query optimization. Ordinarily, the database uses an index to find the 'Row ID' and then goes to the table to fetch the actual data. If the index itself includes the data you're asking for (e.g., an index on `(name, email)` satisfying a query for `SELECT email ... WHERE name=...`), the database can stay within the index. This 'Index-Only Scan' saves significant disk I/O and is one of the most effective ways to tune high-latency 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')?