A developer rewrites a query so the WHERE conditions appear in the same order as the columns of a multi-column index, expecting a faster plan. Does the textual order of predicates in the WHERE clause affect index usage? Explain.
answer
- AND is commutative — optimizer sees a set
- Index key order is the fixed one
- Sargable: bare column on one side
- Fix the index, not the text
- ORDER BY order matters; WHERE order does not
basics
~20 sNo. The optimizer normalizes the conjunction, so the order you type conditions in does not matter. What matters is which columns are constrained and how (equality vs range) versus the index key order, which is fixed at creation.
solid answer
~60 sThe textual order of ANDed conditions is irrelevant. The optimizer parses the predicate list into a set, then matches that set against each candidate index's key columns; reordering the text produces an identical plan. What actually decides index usage is (1) **which** columns are constrained, (2) **how** they are constrained — equality, range, inequality, function-wrapped — and (3) the **column order chosen when the index was created**, which is the only ordering the engine cannot rearrange. So the fix for a bad plan is never to shuffle the WHERE clause; it is to change the index key order, add the missing leading column to the predicate, or rewrite a predicate that is not sargable (searchable), for example one that wraps the indexed column in a function. A caveat: this applies to conjunctions of predicates. `OR` and correlated subqueries change plan shape for real reasons, and in some engines evaluation order of expensive functions can matter for CPU cost — but neither is about the order you typed the AND-terms in.
go deeper
Answer the question directly — no, order of ANDed conditions does not matter — and name the one ordering that does: the column order chosen when the index was created.
Add the mechanism: the optimizer canonicalizes the conjunction into a set, then matches it to index prefixes; also mention that equality versus range predicates matter more than any textual position.
Redirect the developer to the real levers — index key order, sargability, missing leading column — and note the narrow exceptions (OR shapes, expensive residual filters) so the advice is not overclaimed.
Use it as a teaching moment about declarative semantics: encourage a team norm where plan problems are diagnosed from the plan and index definitions, not from folklore rewrites of query text.
## The myth A persistent belief is that a query "matches" an index by textual position: put `WHERE a = ? AND b = ?` in the same order as `INDEX (a, b)` and the engine will find it; write `WHERE b = ? AND a = ?` and it will not. This is false in every mainstream relational engine, and it is worth being able to explain why with confidence. ## What the optimizer actually does SQL is declarative: the statement describes the result set, not the procedure. Before any index is considered, the query goes through parsing and normalization. A conjunction (`X AND Y AND Z`) is a logical AND, which is commutative and associative, so the optimizer canonicalizes it into an unordered set of conjuncts — often after constant folding and simplification. Then access-path selection asks, per candidate index: how many of my leading key columns are constrained by conjuncts in this set, and by what kind of predicate? That is a set-membership question. Rewriting `b = 2 AND a = 1` as `a = 1 AND b = 2` changes nothing in the set, so the cost estimates and the chosen plan are byte-for-byte identical. ## What does matter **Which columns are constrained.** Only predicates on a leading prefix of the index key can drive a seek. If your index is `(a, b)` and you constrain only `b`, no amount of reordering saves you. **How they are constrained.** Equality (`=`, and often `IN`) keeps the prefix chain alive for the next column. A range (`>`, `<`, `BETWEEN`, `LIKE 'x%'`) can be used for the seek boundary but the columns after it can no longer be used for seeking. `!=`, `NOT IN` and leading-wildcard `LIKE '%x'` generally cannot seek at all. **Sargability.** Wrapping the indexed column in an expression — `WHERE UPPER(email) = ?`, `WHERE created_at + interval '1 day' > ?`, or an implicit type cast on the column side — hides the column from the index, because the index stores the raw column values, not the function's output. Rewriting the predicate so the bare column stands alone on one side is a real fix; reordering is not. **The index key order.** This is the one ordering that is fixed and physical. It is decided at `CREATE INDEX` time, and it is the thing you change when you want a different access path. ## Why the myth survives Three reasons. First, some documentation and blog examples happen to list WHERE terms in index order for readability, which reads as a requirement. Second, developers coming from key-value stores or from ORM query builders that generate a composite key string do have order-sensitive lookups there. Third, people conflate the WHERE clause with the `ORDER BY` clause — where order genuinely is semantic — or with the column list inside `CREATE INDEX`, where order genuinely is decisive. ## The narrow exceptions - `OR` is not the same as `AND`: a disjunction may force a different plan shape (union of index scans, or a full scan) — that is about the operator, not the order. - Some engines evaluate residual filter expressions roughly in written order, so putting an extremely expensive user-defined function last can shave CPU. This affects filter cost, never index selection. - Predicate order in the text can influence which of two equal-cost plans a planner picks by tiebreak in rare implementations; treat that as noise, not a tuning technique. ## How to answer Say plainly: no, AND-term order is irrelevant because the optimizer treats the conjunction as a set; the orderings that matter are the index key order and the equality-then-range structure of the predicates. Then name the real fixes: reorder the index, not the query; make the predicate sargable; supply the missing leading column.
- If WHERE order does not matter, what rewrite of a query *does* change whether an index can be used?Removing a function or cast that wraps the indexed column — `WHERE UPPER(email) = ?` cannot seek an index on the raw `email` column, while `WHERE email = ?` can. Replacing a leading-wildcard `LIKE '%foo'` with a prefix match, or converting an `OR` over two columns into a `UNION` of two seekable branches, are the other classic ones. All of these change which columns the engine can match against index keys, unlike shuffling AND-terms.
- Does the order of columns in an ORDER BY clause matter to index usage?Yes, and this is the contrast that makes the WHERE answer memorable. `ORDER BY a, b` and `ORDER BY b, a` request different result orders, so they are different queries, and only an index whose key order matches the requested order can supply the rows pre-sorted. WHERE terms are a filter set; ORDER BY terms are a sequence.
saying these in an interview costs you the question
- Claiming the WHERE clause must mirror the index column order
- Reordering conditions as a tuning step instead of changing the index
- Not distinguishing AND-term order from index key order or ORDER BY order
- Believing the first condition written is always evaluated first at runtime
- Assuming an index will be used just because the column is mentioned somewhere in the predicate