Writing Queries an Index Can Use

An index exists, the column is indexed, and the plan still says SCAN. Almost always the query is written in a way the index cannot serve. The jargon for a condition an index can use is sargable — from "Search ARGument able".

The rule behind all of it: an index stores the column's values, not the values of expressions wrapped around it.

Wrapping the column in a function

emp_id is indexed as the primary key:

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE emp_id = 4;

SEARCH — index used. Now wrap it:

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE abs(emp_id) = 4;

SCAN — every row read. The index knows emp_id values; it has no idea what abs(emp_id) is, so the database must compute it for every row.

Same failure with arithmetic:

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE emp_id + 0 = 4;

The fix: move the work off the column

Rearrange so the column stands alone. emp_id - 1 = 3 scans:

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE emp_id - 1 = 3;

Written as emp_id = 4, the index is used again — the same condition, the same rows, a different plan. That is the whole technique.

Common cases and their rewrites:

Not sargable	Sargable
`WHERE YEAR(placed_at) = 2024`	`WHERE placed_at >= '2024-01-01' AND placed_at < '2025-01-01'`
`WHERE amount * 100 > 5000`	`WHERE amount > 50`
`WHERE LOWER(name) = 'acme'`	a functional index on `LOWER(name)`, or store a normalised column
`WHERE CAST(id AS TEXT) = '4'`	`WHERE id = 4` — fix the type, not the query
`WHERE email LIKE '%example.com'`	full-text search, or store the domain in its own indexed column

The date one is worth committing to memory. YEAR(placed_at) = 2024 and DATE_TRUNC('month', placed_at) = … are how most date filters get written, and both scan. The half-open range from lesson 13 is both correct across time zones and index-friendly.

Leading wildcards

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE email LIKE '%example.com';

SCAN, and no index can help. A B-tree is sorted by the start of the value, so without a known prefix there is nowhere to begin. LIKE 'ada%' is fine; LIKE '%ada%' is a scan on every database.

If you genuinely need it: full-text search, a trigram index (PostgreSQL's pg_trgm), or storing the reversed string and matching a prefix on that.

OR across different columns

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE emp_id = 1 OR emp_id = 2;

SEARCH — both branches hit the same index, so it works.

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE emp_id = 1 OR dept_id = 2;

SCAN. dept_id has no index, and one unservable branch forces the whole condition to be evaluated row by row. If both columns were indexed, some planners combine the two; others do not. The portable rewrite is a UNION of two sargable queries:

SQL
SELECT * FROM employees WHERE emp_id = 1
UNION
SELECT * FROM employees WHERE dept_id = 2;

Each branch can use its own index. Use UNION rather than UNION ALL here — a row matching both conditions would otherwise appear twice.

IN is fine

SQL
EXPLAIN QUERY PLAN SELECT * FROM employees WHERE emp_id IN (1, 2, 3);

SEARCH. IN over an indexed column is a set of index lookups, not a scan — one more reason to prefer it to a chain of ORs.

NOT, <>, and IS NULL

<> and NOT IN are rarely sargable: "everything except this" usually means most of the table, and a scan is genuinely cheaper.
IS NULL is indexable on PostgreSQL, SQL Server and SQLite, which store NULLs in the index. Oracle's B-trees do not index NULLs at all, so IS NULL always scans there — a classic Oracle-specific surprise.

Implicit type conversion

The subtlest one. If a column is VARCHAR and you compare it to a number, the database may convert the column rather than the literal — applying a function to every row, and losing the index exactly as abs() did. Nothing in the query looks wrong.

The fix is to compare like with like: quote the string, or fix the column type. On MySQL this is a frequent cause of "the index exists but is not used".

Common mistakes

YEAR(date_col) = 2024 — the most common non-sargable filter in existence.
Arithmetic on the indexed column — move it to the other side.
LIKE '%x%' — no index can serve it.
OR across an unindexed column — one bad branch spoils the whole condition.
Comparing a string column to a number — silent conversion, silent scan.

Interview question

You have an index on placed_at but WHERE YEAR(placed_at) = 2024 is slow. Why, and how do you fix it?

The function makes it non-sargable — the index stores placed_at, not YEAR(placed_at), so every row must be computed. Rewrite as a half-open range, or add a functional index on the expression. Naming both options, and preferring the range, is the complete answer.

Module complete

You can now read a plan, tell a scan from a seek, design a composite index for a query, and spot the conditions that quietly disable one.

Next: transactions — what happens when more than one person writes at the same time.