Database Indexing medium
Why does `WHERE YEAR(created_at) = 2023` fail to use a standard index on `created_at`?
- A SQL functions are executed on the client application side, which prevents the database engine from ever seeing the resulting computed values.
- B The B-Tree indexes raw values; applying a function 'hides' the data from the index's sort order, forcing the database to perform a full table scan.
- C Temporal data types like 'Date' and 'Timestamp' are fundamentally incompatible with B-Tree indices and must utilize specialized R-Tree structures.
- D The query automatically triggers a 'Composite Index' scan that defaults to the leftmost column regardless of the filtering predicate provided.
Answer: B. The B-Tree indexes raw values; applying a function 'hides' the data from the index's sort order, forcing the database to perform a full table scan.
To use an index, the database must compare the value in the index directly to your filter. By wrapping the column in a function like `YEAR()`, you are asking the database to calculate a new value for every single row *before* performing the comparison. This 'SARGability' issue destroys performance. The correct solution is to rewrite the query as a range: `WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01'`, or to create a 'Function-Based Index' on `YEAR(created_at)`.
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')?