Function Based Index Concept

Indexing a computed expression, not the raw column

A function-based index (also called an "expression index") is built on the result of applying a function or expression to a column, rather than on the raw column value itself.

sql
CREATE INDEX idx_email_lower ON students (LOWER(email));

Why you'd need this

Suppose your application does case-insensitive email lookups:

sql
SELECT * FROM students WHERE LOWER(email) = 'jane.doe@example.com';

Even if a perfectly good regular index exists on the raw email column, this query cannot use it. Why? Because a regular index on email is sorted by the raw stored values of email (e.g., Jane.Doe@Example.com), not by their lowercased forms. The optimizer has no way to know that LOWER(email) = 'jane.doe@example.com' corresponds to any particular position in an index sorted by raw, mixed-case values — so it falls back to a full table scan, checking LOWER(email) against every single row.

A function-based index solves this directly: it builds the index on the already-computed LOWER(email) values. Now the index is sorted by lowercased email, and the query's LOWER(email) = '...' filter matches the index's actual stored (function-applied) values exactly — allowing a fast index lookup instead of a full scan.

The general principle

Any time a query consistently wraps an indexed column in a function or expression in its WHERE clause — UPPER(name), YEAR(created_at), price * quantity, SUBSTRING(code, 1, 3) — a regular index on the raw column cannot help, because the stored, sorted index values don't match what the query is actually comparing against. A function-based index on that exact expression bridges the gap.

Edge cases

• The expression in the query must match the expression used to build the index exactly (or in a way the optimizer can recognize as equivalent) — WHERE LOWER(email) = ... will use an index on LOWER(email), but WHERE UPPER(email) = ... will not, since it's a different expression.
• Not every dialect supports this identically — PostgreSQL supports arbitrary expression indexes; MySQL (from version 8.0+) supports "functional key parts"; older MySQL versions required a workaround using a generated/virtual column plus a regular index on that column.
• Function-based indexes carry the same write overhead as any index — the function must be re-evaluated and the index updated on every relevant write, which can add computational (not just I/O) cost if the function is expensive.

Interview-style Q&A

Q: Why can't a regular index on `email` serve a query filtering on `LOWER(email)`? A: Because the regular index is sorted by the raw, unmodified email values, while the query is comparing against a computed lowercase form — the sort orders don't align, so the optimizer can't use the index for that comparison.

Q: What's the general rule for when you need a function-based index? A: Whenever a query consistently applies the same function/expression to a column in its filter condition, and you want that filter to use an index rather than fall back to a full scan.

Key takeaway: an index is only useful if it's sorted the way the query actually compares values — a function-based index re-aligns the index's sort order with the query's real comparison expression.