SQL / INDEXES AND QUERY PERFORMANCE
Sargable predicates and the functions that defeat indexes
Rewrite predicates that wrap an indexed column in a function or arithmetic into ranges the index can seek, and index the expression when you can't.
What you will learn
- Spot non-sargable predicates: the indexed column wrapped in a function or arithmetic
- Convert date-function filters into half-open ranges: >= start AND < next start
- Move arithmetic and casts to the literal side so the column stays bare
- Add an expression index when the transformation cannot be inverted
Understanding Sargable predicates and the functions that defeat indexes
An index is an ordered copy of the values exactly as they are stored in the column, so the only question it can answer is where a given stored value sits in that order. Write WHERE date(placed_at) = '2024-03-01' and the thing you are comparing, the date part on its own, appears nowhere in that ordered copy, so the engine has no entry point and must read every row to compute date(placed_at) for it. A predicate that leaves the column untouched can instead be handed to the index as a search argument, which is where the awkward word sargable comes from. It does not matter that date() happens to preserve order: the planner has no general way to know a function is monotonic, so it never assumes it.
The mechanical fix is to keep the column bare on one side and move the inverse of the transformation to the literal side, where it is evaluated once for the whole query instead of once per row. That is why WHERE placed_at >= date('now','-7 days') is perfectly sargable while WHERE date(placed_at) >= date('now','-7 days') is not: same function, different side. A filter on a month becomes a half-open range, amount * 100 > 5000 becomes amount > 50, and LIKE 'ada%' is already a range in disguise, everything from 'ada' up to but not including 'adb', which is why prefix patterns can seek and '%@example.com' never can. Doing that algebra is your job rather than the engine's because it is not always safe: dividing by a negative constant flips the operator, and a cast can change which rows match.
Some transformations have no inverse at all, such as lower(email), a hash, or a JSON extraction, and then you index the expression itself: CREATE INDEX users_email_lower ON users (lower(email)) in SQLite or PostgreSQL, a functional index in MySQL 8.0.13 and later, or an index over a stored generated column elsewhere. The query then has to spell the expression the way the index does, since lower(trim(email)) will not match an index on lower(email). Keep in mind that sargable only means the index can be used: if the predicate matches most of the table, a scan is genuinely cheaper and the planner is right to choose it.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
placed_at TEXT NOT NULL,
total REAL NOT NULL
);
INSERT INTO orders (placed_at, total) VALUES
('2024-02-29 23:10:00', 40.00),
('2024-03-01 00:05:00', 12.50),
('2024-03-15 13:40:00', 99.00),
('2024-03-31 23:59:59', 7.25),
('2024-04-01 00:00:01', 55.00);
CREATE INDEX orders_placed_at ON orders (placed_at);
-- non-sargable: placed_at is buried inside substr()
EXPLAIN QUERY PLAN
SELECT total FROM orders WHERE substr(placed_at, 1, 7) = '2024-03';
-- sargable: the same three rows, expressed as a range over the bare column
EXPLAIN QUERY PLAN
SELECT total FROM orders WHERE placed_at >= '2024-03-01'
AND placed_at < '2024-04-01';An index is a sorted list of stored column values, so it can only be searched when the column appears bare on one side of the comparison.
Worked examples
Move the arithmetic to the constant side
Shows that dividing the literal instead of multiplying the column returns an identical row set while leaving the column indexable.
CREATE TABLE invoices (id INTEGER PRIMARY KEY, amount REAL NOT NULL);
INSERT INTO invoices (id, amount) VALUES (1, 90.0), (2, 91.0), (3, 99.0);
CREATE INDEX invoices_amount ON invoices (amount);
SELECT 'wrapped', id FROM invoices WHERE amount * 1.1 > 100.0 ORDER BY id;
SELECT 'bare', id FROM invoices WHERE amount > 100.0 / 1.1 ORDER BY id;Example explained
Line 1amount * 1.1 > 100.0 hides the indexed column behind a multiply, so all three rows must be read and multiplied.
Line 2amount > 100.0 / 1.1 folds the division once into the bound 90.909..., leaving amount bare as a lower bound the index can descend to.
Line 3Both forms return rows 2 and 3, which is the requirement: a sargable rewrite must be an identity, not an approximation.
Line 4Dividing by a positive constant keeps the direction of >; with -1.1 you would have to flip it to <, which is why the engine will not do this for you.
A range that does not lose the last day
Demonstrates the boundary bug in the usual BETWEEN rewrite of a month filter on a timestamp column.
CREATE TABLE orders (id INTEGER PRIMARY KEY, placed_at TEXT NOT NULL);
INSERT INTO orders (id, placed_at) VALUES
(1, '2024-03-01 00:00:00'),
(2, '2024-03-31 09:15:00'),
(3, '2024-04-01 00:00:00');
SELECT 'substr', count(*) FROM orders WHERE substr(placed_at, 1, 7) = '2024-03';
SELECT 'between', count(*) FROM orders WHERE placed_at BETWEEN '2024-03-01' AND '2024-03-31';
SELECT 'half-open', count(*) FROM orders WHERE placed_at >= '2024-03-01' AND placed_at < '2024-04-01';Example explained
Line 1substr(placed_at, 1, 7) = '2024-03' gives the right answer, 2 rows, but cannot use an index on placed_at.
Line 2BETWEEN '2024-03-01' AND '2024-03-31' is sargable and wrong: '2024-03-31 09:15:00' sorts after the bare date '2024-03-31', so a whole day disappears with no error.
Line 3The half-open form matches the same 2 rows and still leaves placed_at bare, so it is both exact and seekable.
Line 4This works because these values are compared as text and fixed-width ISO-8601 text sorts chronologically; a format like '31/03/2024' would break the equivalence.
Index the expression when it has no inverse
Shows an expression index making a lower(email) lookup seekable in SQLite.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL,
display_name TEXT NOT NULL
);
INSERT INTO users (id, email, display_name) VALUES
(1, 'Ada@Example.com', 'Ada L'),
(2, 'grace@example.com', 'Grace H');
CREATE INDEX users_email_lower ON users (lower(email));
EXPLAIN QUERY PLAN
SELECT display_name FROM users WHERE lower(email) = 'ada@example.com';Example explained
Line 1lower(email) cannot be turned into a range over email, because case folding does not preserve the stored ordering; there is nothing to invert.
Line 2Indexing lower(email) stores the folded values in sorted order, so the identical expression in the WHERE clause becomes a lookup key and the plan reports SEARCH.
Line 3<expr> is simply how SQLite names an indexed expression in a plan; it is the index column, not a placeholder you write.
Line 4The match is syntactic: lower(trim(email)) or upper(email) in the query would fall back to a full scan of users.
Important notes
In PostgreSQL a LIKE 'prefix%' seek needs an index built with text_pattern_ops or in the C collation; under a locale-aware collation the planner cannot turn the pattern into a range.
ORDER BY and JOIN conditions obey the same rule: ORDER BY lower(name) cannot read an index on name in order, so it forces an explicit sort.
Common mistakes
Filtering with WHERE strftime('%Y', placed_at) = '2024' (or YEAR(), DATE_TRUNC(), CAST(... AS DATE)): the results are right but the index on placed_at is unusable, so cost grows with table size and a query that is instant on 500 development rows takes minutes on five million.
Rewriting a month filter as BETWEEN '2024-03-01' AND '2024-03-31' on a timestamp column: now sargable but silently wrong, since every row timestamped after midnight on the 31st is dropped and nothing raises an error.
Comparing an indexed text column to a number, such as WHERE account_no = 12345 where account_no is VARCHAR: MySQL converts the column to a number rather than the literal to a string, so it evaluates a conversion on every row and skips the index, a function you never typed.
Try it yourself
Change, predict, then run
In a browser SQLite editor create events(id INTEGER PRIMARY KEY, occurred_at TEXT, payload TEXT) with a few 2024 and 2025 timestamps and an index on occurred_at, then run EXPLAIN QUERY PLAN for WHERE strftime('%Y', occurred_at) = '2024'. Rewrite the predicate so the plan changes from SCAN to SEARCH, and confirm both versions return the same rows including one timestamped 2024-12-31 23:59:59.
Open the SQL workspaceCheck your understanding
An index exists on invoices(amount). WHERE amount * 100 > 5000 and WHERE amount > 50 select the same rows, but only the second can use the index. What is the real cost of the first version?
- The multiplication runs once per row, and that CPU work is what makes the query slow.
- The optimiser rewrites the first form into the second, but only after the table has been analysed.
- The index holds amount values, not amount * 100 values, so there is no entry to descend to and every row must be read and computed.
- The literal 5000 falls outside the index's stored numeric range, so the index is skipped.
Show answer
The index is a sorted structure over the stored amount values, so a bound on amount * 100 matches nothing in it and the engine has to fall back to reading the whole table. Option 0 is tempting because the multiply genuinely does run per row, but a floating-point multiply costs nanoseconds; the damage is the lost seek, which turns a few page reads into a full scan. Engines generally do not perform the rewrite themselves, since moving a constant across a comparison is not safe for every operator, sign or data type.