Database Indexing medium
You have a `VARCHAR(5000)` column. Why might you use a Prefix Index `(col(20))` instead of a full index?
- A To enable native support for complex regular expression searches and partial string matching that are typically only available in specialized full-text search engines.
- B To significantly reduce the size of each index entry, allowing more keys per page (higher fan-out) and ensuring the index remains in the RAM-based buffer pool.
- C To enforce global case-insensitivity across several disparate partitions without requiring the use of specialized database collations or manual string transformations.
- D Prefix indexes are a mandatory requirement for indexing JSON or XML data types in most modern relational databases to ensure structural validity.
Answer: B. To significantly reduce the size of each index entry, allowing more keys per page (higher fan-out) and ensuring the index remains in the RAM-based buffer pool.
Indexing massive columns (like 5,000-character descriptions) creates an enormous B-Tree that consumes vast amounts of storage and RAM. In many cases, the first 20 or 30 characters are 'unique enough' to distinguish between rows. By using a Prefix Index, you get a much smaller, faster index structure. If there is a collision in the index (e.g., two strings starting with the same 20 chars), the database simply goes to the main table to check the full value.
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')?