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

  1. With an index on order_date, strftime('%Y-%m', order_date) = '2024-02' gives SCAN orders: the index can't be used.
  2. The same filter as a range on the bare column gives SEARCH … (order_date>? AND order_date<?).
  3. A composite index (customer_id, order_date) is searched for customer_id = 1 AND order_date >= ….
  4. For order_date >= … alone, it can't be used: the dates are only sorted within each customer.
  5. Selecting only customer_id and order_date lets the index answer on its own: a covering index.

Key points

Common mistakes

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.