You created the right index and the query still scans the whole table. Nine times out of ten the index is fine and the predicate is the problem: something in the WHERE clause hides the column from the index. A predicate an index can search on is called sargable — from Search ARGument ABLE — and writing sargable conditions is the cheapest performance fix there is: same rows, same index, no schema change. This lesson lists the patterns that break sargability, shows the rewrite for each, and explains the rule underneath them all.

The rule

An index is sorted by the column's stored values. The server can seek only if it can turn your condition into "find this value, or this range of values, in that order". So:

Leave the indexed column bare on one side of the comparison, and put all the computation on the other side.

placed_on >= '2024-01-01' — bare column, constant: sargable. YEAR(placed_on) = 2024 — the index is not sorted by YEAR(placed_on), so the server must compute it for every row: not sargable.

Functions on the column

-- non-sargable: YEAR() on every row, type ALL
SELECT id, amount FROM orders WHERE YEAR(placed_on) = 2024;

-- sargable: a range on the bare column, type range
SELECT id, amount FROM orders
WHERE placed_on >= '2024-01-01' AND placed_on < '2025-01-01';

The same shape covers months and days: MONTH(d) = 3 AND YEAR(d) = 2024 becomes d >= '2024-03-01' AND d < '2024-04-01'; DATE(created_at) = '2024-06-30' becomes created_at >= '2024-06-30' AND created_at < '2024-07-01'.

Prefer the half-open range (>= start, < next start) to BETWEEN. On a DATETIME, BETWEEN '2024-01-01' AND '2024-12-31' means up to 2024-12-31 00:00:00 and silently drops every order placed later that day. The half-open form is correct for DATE and DATETIME alike and never overlaps the next period.

Other common functions with sargable twins:

Arithmetic on the column

Move the arithmetic to the constant side, as in algebra:

WHERE price * 1.18 > 1180          -- non-sargable
WHERE price > 1180 / 1.18          -- sargable: price > 1000

WHERE created_at + INTERVAL 7 DAY > '2024-06-30'      -- non-sargable
WHERE created_at > '2024-06-30' - INTERVAL 7 DAY      -- sargable

Watch the sign when you divide by a negative number, and watch the boundary: DATEDIFF(a, d) <= 30 and d >= a - 30 days agree only when d is not after a. A rewrite must return the same rows — test it on the edge values, as the exercises do.

LIKE and wildcards

A B-tree can serve a LIKE whose pattern starts with literal characters: name LIKE 'ravi%' is a range — every entry from 'ravi' up to just before 'ravj'. A leading wildcard cannot: '%ravi' and '%ravi%' match entries scattered all over the index.

WHERE email LIKE 'ravi%'        -- range scan on idx_email
WHERE email LIKE '%@gmail.com'  -- full scan

For suffix searches, store the reversed string in an indexed column, or a separate indexed domain column. For words inside prose, use a FULLTEXT index with MATCH(body) AGAINST('index').

Implicit type conversions

Comparing a column with a value of a different type makes MySQL convert, and if it is the column that gets converted, the index is lost:

-- phone is VARCHAR(15) with an index
WHERE phone = 9876543210        -- number: every phone converted to a number, full scan
WHERE phone = '9876543210'      -- string: index lookup

The same trap hides in joins: joining a VARCHAR column to an INT column, or two string columns with different character sets or collations, can stop the index on the inner table being used. Keep join columns the same type, length, charset and collation.

The opposite direction is harmless: an INT column compared with the string '42' converts the constant once and still uses the index.

OR, NOT and negative conditions

• WHERE a = 1 OR b = 2 on two different columns cannot be one seek. MySQL may use index_merge (union of two index scans) or scan; rewriting as UNION of two sargable queries is a classic fix for a hot query.
• WHERE a = 1 OR a = 2 is fine — it is a IN (1, 2), a set of seeks.
• !=, NOT IN and NOT LIKE usually match most of the table, so the optimizer scans regardless; that is selectivity, not syntax.
• IS NULL is sargable in MySQL — the nulls are stored in the index (ref_or_null when combined with = value).

When the expression cannot move: functional indexes

Sometimes the expression is the real key — case-insensitive matching on a case-sensitive collation, or JSON fields. MySQL 8.0.13+ can index an expression directly:

CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));
SELECT id FROM users WHERE LOWER(email) = 'asha@example.com';   -- now sargable

The query must use the same expression as the index. Before 8.0.13 the equivalent was a generated column with an index on it. PostgreSQL has had expression indexes for a long time: CREATE INDEX ON users (lower(email));.

Sargable is necessary, not sufficient

A sargable predicate lets the optimizer consider the index. It still chooses by cost: if the range covers a large share of the table, a scan wins, and that is correct. Sargability is about not taking the choice away from it.

Practise

Both exercises hand you a non-sargable query and ask for a sargable one that returns exactly the same rows — including the edge rows a careless rewrite gets wrong, such as an order at 23:30 on the last day of the year.