Database Indexing easy
In a Clustered Index (InnoDB Primary Key), where is the table data stored?
- A In a separate, high-performance heap file that is referenced by internal memory pointers.
- B On a specialized, read-only SSD partition that is isolated from the main data storage blocks.
- C Directly within the leaf nodes of the B-Tree structure; in this model, the index IS the physical table.
- D Strictly within the database's sequential transaction log to ensure maximum write-throughput.
Answer: C. Directly within the leaf nodes of the B-Tree structure; in this model, the index IS the physical table.
In a 'Clustered Index' (the default for InnoDB in MySQL), the physical layout of the table reflects the order of the Primary Key. When the database finds a row in the index, it already has the data right there in its hands. This eliminates the need for a secondary 'Table Lookup' (bookmark lookup), making PK lookups incredibly fast. However, it also means that changing a PK is expensive, as the row must be physically moved on the disk to maintain the sort order.
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')?