In this lesson, you will learn how to boost query performance by indexing the results of expressions rather than raw column values.
The Problem: The "Non-Sargable" Trap
You have learned previously about B-Tree internals and how the optimizer uses them to perform fast lookups. However, a common performance pitfall occurs when you apply a function to a column in your WHERE clause.
Consider a users table where you want to find all accounts created in a specific year. You might write:
SELECT * FROM users WHERE YEAR(created_at) = 2023;
Because the database must calculate YEAR() for every row in the table before it can compare the result to 2023, it cannot utilize a standard index on created_at. It is forced to perform a full table scan, rendering your index useless.
The Solution: Functional Indexing
Functional indexing (also known as expression-based indexing) allows you to index the result of an expression. By creating an index on the function itself, the database stores the computed values in a B-Tree structure ahead of time.
In MySQL 8.0+ and MariaDB, you can define this index directly:
ALTER TABLE users ADD INDEX idx_created_year ((YEAR(created_at)));
When you run your query now, the optimizer recognizes that the expression YEAR(created_at) matches the definition of idx_created_year. It performs an index seek on the pre-computed values, turning an expensive linear scan into a highly efficient jump.
When to Use It
Functional indexing is a powerful tool for:
- Normalization mismatches: Indexing
LOWER(email)to support case-insensitive lookups without scanning every row. - JSON Extraction: Indexing specific fields extracted from a JSON column (e.g.,
(CAST(data->>'$.id' AS UNSIGNED))). - Complex Arithmetic: Indexing calculations like
(price * tax_rate)if you frequently filter by the final calculated total.
Be aware that these indexes carry a trade-off: every INSERT or UPDATE must now compute the function result to maintain the index, so use them specifically for columns frequently filtered by that exact expression.
Key Takeaway: Functional indexing allows the optimizer to use B-Tree lookups on computed values, transforming slow "non-Sargable" queries into high-performance index seeks.