Database Indexing easy
Which `LIKE` query pattern renders a standard B-Tree index useless?
- A Queries utilizing a simple prefix match: `name LIKE 'Dav%'`
- B Queries utilizing a leading wildcard match: `name LIKE '%id'`
- C Queries utilizing an exact literal comparison: `name = 'David'`
- D Queries utilizing a standard numeric range filter: `id > 500`
Answer: B. Queries utilizing a leading wildcard match: `name LIKE '%id'`
B-Tree indexes are sorted alphabetically from left to right. A prefix query like `Dav%` can jump directly to the 'D' section of the tree and scan forward. However, a leading wildcard like `%id` means the target characters could be anywhere in the string; the database has no starting point to 'seek' to. Consequently, it must abandon the index and perform a Full Table Scan to check every individual row.
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')?