Database Indexing medium
In a Clustered Index (like MySQL-InnoDB Primary Key), how is data physically stored compared to a secondary index?
- A The index leaf nodes contain the full row data, effectively sorting the physical table storage by the Primary Key value.
- B The index is stored in a separate, unordered 'Heap' metadata structure that uses persistent pointers to reference the main table.
- C The index stores a memory-mapped pointer to the row in virtual memory, allowing for O(1) retrieval speeds for all active processes.
- D Clustered indexes are specialized columnar stores that prioritize high-frequency aggregations over standard row-based lookups.
Answer: A. The index leaf nodes contain the full row data, effectively sorting the physical table storage by the Primary Key value.
In a Clustered Index (the default for InnoDB in MySQL), the index NOT ONLY points to the data; it IS the data. The rows are physically stored on disk in the same order as the index. Consequently, searching by Primary Key is a single B-Tree lookup. In contrast, a Secondary Index contains a pointer (usually the PK itself) to the Clustered Index, necessitating a second lookup (bookmark lookup) to retrieve the full row if the query isn't covered by the index.
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')?