Index-friendly queries: sargable filters and composite indexes
A filter is sargable when the database can use an index for it: the indexed column stands alone on one side of the comparison. A composite index on (a, b) helps queries that filter on a, or on a and b, but not on b alone.
Why it matters
An index can exist and still not be used. WHERE strftime('%Y-%m', order_date) = '2024-02' reads every row even with an index on order_date. Knowing the rules lets you write the same filter in a way that uses the index.
The idea
The index stores the raw column values in order. If you wrap the column in a function (UPPER(name), strftime(…)), do maths on it (salary + 0 > 60000), or start a LIKE with % ('%son'), the database can't search the sorted values, so it scans. Rewrite the condition so the bare column is compared with a value: a date range instead of a function.
A composite index on (customer_id, order_date) is sorted by customer, then by date within each customer — like a phone book sorted by surname, then first name. It helps customer_id = 1, and customer_id = 1 AND order_date >= …, but not order_date >= … alone: the leftmost-prefix rule.
If the index contains every column the query needs, it is a covering index, and the table itself isn't touched.
A phone book sorted by surname, then first name. Finding "Smith, John" is quick; finding all the Smiths is quick; finding everyone called John is not — you would have to read the whole book.
A person might notice that the Johns are easy to find if there are only five surnames. Some databases can do a "skip scan" too, but don't count on it — design the column order for the queries you really run.
Syntax
-- not sargable: the column is inside a function
WHERE strftime('%Y-%m', order_date) = '2024-02'
-- sargable: the bare column, compared with a range
WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01'
CREATE INDEX idx ON orders (customer_id, order_date);How it works
- With an index on order_date,
strftime('%Y-%m', order_date) = '2024-02'gives SCAN orders: the index can't be used. - The same filter as a range on the bare column gives SEARCH … (order_date>? AND order_date<?).
- A composite index (customer_id, order_date) is searched for
customer_id = 1 AND order_date >= …. - For
order_date >= …alone, it can't be used: the dates are only sorted within each customer. - Selecting only customer_id and order_date lets the index answer on its own: a covering index.
Key points
- Keep the indexed column bare: no functions, no maths, no leading % in LIKE.
- Rewrite "same month" or "same year" filters as half-open ranges: >= start AND < next start.
- A composite index (a, b) serves filters on a, or a and b — not b alone (leftmost prefix).
- Put the column you test with = first, and the range column after it.
- A covering index holds every column a query needs, so the table isn't read at all.
Common mistakes
- Writing
WHERE YEAR(order_date) = 2024on an indexed date column: the function hides the column, so the index can't be used. - Creating (order_date, customer_id) when the queries filter by customer first.
- Comparing a column with a value of another type (a number column with '42'), which can force a conversion on every row.
Not true: WHERE YEAR(order_date) = 2024 can still use an index on order_date.
In fact: The function hides the column, so the index cannot be used and every row is checked. Write a range instead: order_date >= '2024-01-01' AND order_date < '2025-01-01'.
In the interview
An index can only be used when the predicate is sargable: the indexed column appears bare, compared with a constant or a range. So I avoid functions or arithmetic on indexed columns, and rewrite things like YEAR(date) = 2024 as a date range. For composite indexes the leftmost-prefix rule applies: an index on customer_id then order_date serves filters on customer, or on customer and date, but not on date alone. So I put equality columns first and range columns last. When a hot query needs only a few columns, I make the index covering.
Part of Unit 7: Performance: indexes and query plans in the free SQL course.