EXPRESSION INDEXIndexes & Search
Buy Me A Coffee

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

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 8.0.13 If you run MySQL < 8.0.13: use a generated column (VIRTUAL or STORED) and index that column.
PostgreSQL ✓ Supported 9.3
SQL Server ✓ Supported 2008 SQL Server supports indexes on computed columns (use PERSISTED for storage). Filtered indexes also available for conditional expressions.
Oracle ✓ Supported 12c
SQLite ✓ Supported 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: Since 8.0.13 PostgreSQL: Since 9.3 SQL Server: Since 2008 Oracle: Since 12c SQLite: Since 3.9.0

Per-Database Syntax & Notes

MySQL

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

CREATE INDEX idx_expression ON table_name(LOWER(email));

SQL Server

SQL Server supports indexes on computed columns (use PERSISTED for storage). Filtered indexes also available for conditional expressions.

CREATE INDEX idx ON table(LOWER(email)); -- Or on a computed column: ALTER TABLE table ADD ComputedCol AS LOWER(email) PERSISTED; CREATE INDEX idx ON table(ComputedCol);

Oracle

CREATE INDEX idx_expression ON table_name(LOWER(email));

SQLite

Expression indexes supported starting in SQLite 3.9.0.

CREATE INDEX idx ON table(LOWER(email));