Function-Based Indexes

Every developer has at least once been puzzled why a simple condition like WHERE lower(email) is not using an index created on the email column. The usual answer is explaining that indexes for these conditions can't be used. But this knowledge is long outdated. You can create a special index for any transformation to a column. Depending on the database used, it's called a function-based index, functional index or an index on an expression.

Usage

MySQL

-- Will not use the index
CREATE INDEX users_email ON users (email);
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- Will use the index (function needs to be wrapped in parentheses)
CREATE INDEX users_email_lower ON users ((lower(email)));
SELECT * FROM users WHERE lower(email) = 'test@example.com';

PostgreSQL

-- Will not use the index
CREATE INDEX users_email ON users (email);
SELECT * FROM users WHERE lower(email) = 'test@example.com';

-- Will use the index
CREATE INDEX users_email_lower ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'test@example.com';

Detailed Explanation

When creating an index on a column, a B⁺-tree is created for all the saved email addresses. All values are stored in a kind-of sorted dictionary for faster searching matching records instead of scanning the complete table.

But if you use a function on the column, these stored exact values in the index can't be used for a comparison: The lower() function is transforming the email address to a different value than stored in the index preventing an index-lookup. Because of this the database needs to scan all values and apply the function to every value to do the comparison. By creating a specific index for the lower(email) expression, an index-lookup within the stored values in the B⁺-tree is possible.

Additional Resources

• MySQL Documentation: Functional Key Parts for the CREATE INDEX statement.
• PostgreSQL documentation: Indexes on Expressions

Tobias Petry

SQL for Devs Founder