Database Indexing medium
What specifically enables an 'Index-Only Scan' (Covering Index) to be faster than a standard Index Scan?
- A It leverages specialized NVMe SSD storage protocols to bypass the standard operating system filesystem and perform direct hardware-level reads.
- B It completely skips reading the main table (Heap) pages because the required columns are already stored within the index structure itself.
- C It automatically converts the B-Tree index into a high-speed in-memory Hash Map to ensure constant-time O(1) performance for every query.
- D It utilizes secondary CPU cores to parallelize the index traversal, effectively doubling the query throughput for read-only analytical workloads.
Answer: B. It completely skips reading the main table (Heap) pages because the required columns are already stored within the index structure itself.
In a standard index scan, the database uses the index to find a pointer to the main table (the Heap) to retrieve the actual row data. An 'Index-Only Scan' or 'Covering Index' occurs when *every* column referenced in the query is already present in the index. The database can satisfy the query reading only the index pages, which are typically smaller and more likely to be cached in RAM, saving massive amounts of disk I/O.
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')?