🔗 Function-based indexes - database tip

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.