Function-based indexes allow indexing on expressions.
An index on a function or expression applied to a column, such as LOWER(email) or amount * rate. Enables index use for queries that apply the same expression, avoiding full scans. Useful for case-insensitive lookups without changing the column values.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Native | 8.0.13 | If you run MySQL < 8.0.13: use a generated column (VIRTUAL or STORED) and index that column. |
| PostgreSQL | Native | 9.3 | |
| SQL Server | Supported via workaround | - | Workaround since 2005. No direct expression-index syntax. Use an indexed computed column instead. |
| Oracle | Native | 12c | |
| SQLite | Native | 3.9.0 | Expression indexes supported starting in SQLite 3.9.0. |
Details
Function-based indexes can significantly improve query performance for specific types of searches.
Standard Syntax
CREATE INDEX idx_expression ON table_name(LOWER(email));
Version Support
MySQL: Native since 8.0.13
PostgreSQL: Native since 9.3
SQL Server: Supported via workaround since 2005
Oracle: Native since 12c
SQLite: Native since 3.9.0
Per-Database Syntax & Notes
MySQL Native syntax
If you run MySQL < 8.0.13: use a generated column (VIRTUAL or STORED) and index that column.
CREATE INDEX idx_expression ON table_name ((LOWER(email)));
PostgreSQL Native syntax
CREATE INDEX idx_expression ON table_name(LOWER(email));
SQL Server Workaround
Add a computed column for the expression, then create an index on that computed column. Since: 2005. No direct expression-index syntax. Use an indexed computed column instead.
ALTER TABLE table_name ADD email_lower AS LOWER(email) PERSISTED;
CREATE INDEX idx_expression ON table_name(email_lower);
Oracle Native syntax
CREATE INDEX idx_expression ON table_name(LOWER(email));
SQLite Native syntax
Expression indexes supported starting in SQLite 3.9.0.
CREATE INDEX idx ON table(LOWER(email));