What is the WHERE clause actually doing to my rows, and which parts of a predicate can use an index?
The WHERE clause filters the rows before anything else happens, and whether it can use an index depends on how you write the condition.
So what does it actually do? It takes each row that comes out of the tables and keeps it only if the condition is true. That happens right after the rows are read and before the grouping, before the columns are chosen, and before the sorting.
Because it happens so early, it is the cheapest place to throw rows away, and it is the only place where an index can be used. That last point is the one worth understanding properly.
CREATE TABLE tickets (
id INTEGER PRIMARY KEY,
title VARCHAR(160) NOT NULL,
priority INTEGER NOT NULL,
assigned_to INTEGER
);
-- This can use an index on priority.
SELECT * FROM tickets WHERE priority <= 2;
-- This cannot use an index on priority, because the value
-- has been changed before the comparison.
SELECT * FROM tickets WHERE priority + 1 <= 3;
So which parts of a condition can use an index? The rule is that the column has to appear on its own, on one side of a comparison, with nothing wrapped around it.
The word for this is that the condition has to be sargable, which just means that the engine can look it up in the index.
priority <= 2can use an index, because the column stands on its own.priority + 1 <= 3cannot, because the engine has to work out the value for every row before it can compare it.LOWER(status) = 'open'cannot, unless there is an index built on that exact function.title LIKE '%urgent%'cannot use a normal index, because the pattern starts in the middle. The patternLIKE 'urgent%'can.created_at BETWEEN '2026-01-01' AND '2026-02-01'can, because it is really two comparisons on the column.
So the next question in this topic asks for a query with two conditions in it.