Index on Expression (Function-Based)
Index on Expression (Function-Based) is a SQL statement in the Indexes category. Indexes on expressions/functions allow efficient searches on transformed columns (case-insensitive lookups). The syntax is CREATE INDEX idx_lower_email ON users (LOWER(email));. It returns expression-based index. A typical example: -- PostgreSQL: CREATE INDEX idx_lower_email ON users (LOWER(email)); -- Speeds up: SELECT * FROM users WHERE LOWER(email) = 'alice@example.com'; -- Without this index, LOWER(email) would require a full scan -- MySQL 8.0.13+: CREATE INDEX idx_year ON orders ((YEAR(order_date))); SELECT * FROM orders WHERE YEAR(order_date) = 2026; -- SQL Server: CREATE INDEX idx_year ON orders (YEAR(order_date)); -- Oracle: CREATE INDEX idx_year ON orders (YEAR(order_date)); A close relative is CREATE INDEX, which creates an index on one or more columns to speed up query performance. A close relative is DROP INDEX, which removes an existing index from a table. A close relative is COMPOSITE INDEX, which an index on multiple columns. Column order matters — queries use leftmost prefix.