‍

Function-Based Index

‍

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.

‍

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';

‍

50% OFF To Learn Everything About Database Indexes —
You don't have to know all the complicated database internals to make a slow query fast. This book teaches simple rules for creating good indexes - enriched with dozens of illustrations to make all concepts more easy to understand. You have no excuse anymore for your slow queries!

SQL For Devs SPONSOR

‍

This message was sent to phps6dnxqhkm8i24@kill-the-newsletter.com.
If you don't want to receive these emails in the future, you can unsubscribe .

‍