Index-friendly queries: sargable filters and composite indexes

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  I["index on (customer_id, order_date)<br/>1: 01-06, 01-07, 01-08, 02-02, 03-10<br/>2: 01-21, 03-01<br/>3: 02-12, 02-20"] --> A["customer_id = 1<br/>uses it"]
  I --> B["customer_id = 1 AND date from Feb<br/>uses it"]
  I --> C["date from Feb only<br/>can't use it: scan"]

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.

The shape of it

-- 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);

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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.

Worked examples

CREATE INDEX idx_orders_date ON orders(order_date);
EXPLAIN QUERY PLAN
SELECT order_id, amount FROM orders
WHERE strftime('%Y-%m', order_date) = '2024-02';

CREATE INDEX idx_orders_date ON orders(order_date);
EXPLAIN QUERY PLAN
SELECT order_id, amount FROM orders
WHERE order_date >= '2024-02-01' AND order_date < '2024-03-01';

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
EXPLAIN QUERY PLAN
SELECT order_id, amount FROM orders
WHERE customer_id = 1 AND order_date >= '2024-02-01';

CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
EXPLAIN QUERY PLAN
SELECT order_id, amount FROM orders
WHERE order_date >= '2024-02-01';

Must-know 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

• WHERE YEAR(order_date) = 2024 on an indexed date column.
• 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.

In other databases

• PostgreSQL and Oracle support expression indexes, such as CREATE INDEX ON customers (LOWER(email)), which make that exact function sargable. MySQL 8 has functional indexes too.
• PostgreSQL and SQL Server let you add extra non-key columns to make an index covering: INCLUDE (amount).

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  Use The Index, Luke — a guide to database performance for developers

  Markus Winand, free

• Read

  PostgreSQL docs — Multicolumn Indexes

  official documentation, free

• Read

  SQLite docs — Query Planning

  official documentation, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the leftmost-prefix rule with the phone-book analogy.

   Show answer

   A phone book is sorted by surname, then by first name: an index on (surname, first_name).

   • "Smith, John": quick. Find the Smiths, then John among them.
   • All the Smiths: quick. They are all together.
   • Everyone called John: no help at all. The Johns are scattered through every surname, so you would have to read the whole book.

   That is the leftmost-prefix rule. A composite index on (a, b) helps a query that fixes a, or a and b, but not one that only filters on b, because b is only sorted within each value of a.

2. Writefrom a blank page, then run it

   Rewrite WHERE strftime('%Y', hire_date) = '2021' so an index on hire_date could be used, and check the plan.

   Show answer

   Wrapping the column in a function (strftime('%Y', hire_date)) hides it from the index: the database would have to work out the year for every row, so the plan is a SCAN. Say the same thing as a range on the bare column, and the plan becomes a SEARCH on the index. It finds the same people, Chen and Fatima.

   sqlCopy

   CREATE INDEX idx_employees_hire ON employees(hire_date);
   EXPLAIN QUERY PLAN
   SELECT name FROM employees
   WHERE hire_date >= '2021-01-01' AND hire_date < '2022-01-01';

   Try it

   1 row

   id	parent	notused	detail
   3	0	164	SEARCH employees USING INDEX idx_employees_hire (hire_date>? AND hire_date<?)

3. Predictsay it before you run it

   With an index on employees(salary), is WHERE salary + 0 > 60000 sargable? What about WHERE salary > 60000 - 0?

   Show answer

   WHERE salary + 0 > 60000 is not sargable: the column is inside a calculation, so the database can't look it up in the index and scans (SCAN employees). (Adding + 0 is in fact an old trick to stop an index being used on purpose.)

   WHERE salary > 60000 - 0 is sargable: salary stands alone, and the arithmetic is on the constant side, which is worked out once. The plan is a SEARCH.

   sqlCopy

   CREATE INDEX idx_employees_salary ON employees(salary);
   EXPLAIN QUERY PLAN SELECT name FROM employees WHERE salary > 60000 - 0;

   Try it

   1 row

   id	parent	notused	detail
   3	0	203	SEARCH employees USING INDEX idx_employees_salary (salary>?)

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Design one index that helps both "orders for customer 1, newest first" and "orders for customer 1 in March".

   Show answer

   One composite index on (customer_id, order_date), customer first.

   • "Customer 1, newest first": fix customer_id = 1, then read that customer's entries backwards. They are already sorted by date, so there is no sort step (no temp B-tree in the plan, below).
   • "Customer 1 in March": fix customer_id = 1, then take a range on the second column: order_date >= '2024-03-01' AND order_date < '2024-04-01'. Its plan is SEARCH … (customer_id=? AND order_date>? AND order_date<?).

   The order matters: (order_date, customer_id) would only help the second query, and badly. Both plans even say COVERING here, because order_id is the rowid, which every SQLite index carries.

   sqlCopy

   CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
   EXPLAIN QUERY PLAN
   SELECT order_id, order_date FROM orders
   WHERE customer_id = 1
   ORDER BY order_date DESC;

   Try it

   1 row

   id	parent	notused	detail
   3	0	55	SEARCH orders USING COVERING INDEX idx_orders_cust_date (customer_id=?)

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

An index can only be used when the predicate is sargable — the indexed column appears bare, compared with a constant or 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 customer and date, but not date alone, so I put equality columns first and range columns last, and make the index covering when a hot query only needs a few columns.

Follow-up questions

Tick each check when you can do it without notes