Function-Based Indexes

An expression like WHERE LOWER(email) won't use an index created for the email column. By creating a function-based index (functional index) you can use this condition with full index support.

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

Author Tobias Petry

Tobias Petry

SQL for Devs Founder


Are you interested to learn more?

Be notified on future content. Never spam.