Problem Statement
Your search uses LOWER(email) = 'x@y.com'. How do you keep it SARGable without changing callers?
Explanation
Create a functional index on the expression the query uses. Then queries that apply the same function can seek that index directly. This avoids scanning the whole table due to a function on the column.
If your engine supports case-insensitive types or collations, consider them as an alternative so you can index the plain column and keep simpler predicates.
Code Solution
SolutionRead Only
-- PostgreSQL CREATE INDEX idx_users_email_lower ON users(LOWER(email)); -- query SELECT * FROM users WHERE LOWER(email) = 'x@y.com';
Practice Sets
This question appears in the following practice sets:
