Why is WHERE price * 1.2 > 120 non-sargable, and how do you rewrite it?
answer
- the column is inside an expression again
- undo the operation on the other side
- the boundary can be computed once
- mind the sign of the constant
- integer division can move the boundary
basics
~20 sThe column sits inside an arithmetic expression, so the engine must compute price * 1.2 for every row instead of seeking a range. Move the arithmetic to the constant side — WHERE price > 120 / 1.2 — leaving the column bare.
solid answer
~50 sAn index on `price` stores prices, not prices multiplied by 1.2, so `price * 1.2 > 120` forces the engine to read each row and do the multiplication. Because multiplication by a constant is invertible, you can move it across the comparison: `WHERE price > 120 / 1.2`, or better, precompute the boundary and write `WHERE price > 100`. The same move fixes date arithmetic — `WHERE order_date + INTERVAL '30' DAY < CURRENT_DATE` becomes `WHERE order_date < CURRENT_DATE - INTERVAL '30' DAY`. Two rules keep the rewrite honest: **flip the comparison operator when you divide or multiply by a negative constant** (`qty * -2 > 100` becomes `qty < -50`), and watch for integer division or decimal rounding shifting the boundary row. And note the limit of the technique — `WHERE col_a - col_b > 0` becomes `col_a > col_b`, which is tidier but still gives no constant bound.
code
sql · 9 lines-- non-sargable: multiplies every stored price
SELECT id FROM products WHERE price * 1.2 > 120;
-- sargable: boundary computed once, column left bare
SELECT id FROM products WHERE price > 100;
-- flip the operator when the constant is negative
-- qty * -2 > 100 ==> qty < -50
SELECT id FROM stock WHERE qty < -50;go deeper
Recognise that arithmetic around a column counts as wrapping it, exactly like a function call. Practise moving a multiplier or an interval to the other side of the comparison until it is automatic.
Explain why the inverse operation is legal here — the transformation is invertible and monotonic — and name the two conditions that break it: a negative constant and truncating integer arithmetic.
Show that you check equivalence before you ship the rewrite: boundary rows, decimal rounding, integral types. A performance fix that quietly changes the result set is a worse outcome than the slow query.
Argue about where derived values belong. When the same expression is filtered repeatedly, the durable answer is storing or indexing it rather than expecting every author to redo the algebra correctly.
## Why arithmetic on the column blocks the range A B-tree index on `price` holds price values in sorted order. `price * 1.2` is a different set of values that appears nowhere in the index, so the engine has no boundary to seek to; it evaluates the expression once per row over the whole table. This is the same failure as wrapping the column in a function — arithmetic operators are just functions with punctuation for names. The accident usually arrives disguised as domain logic: a VAT multiplier, a unit conversion, a percentage, a "give me rows older than thirty days" clause. It reads naturally in English, which is exactly why it survives review. ## Moving the arithmetic across the comparison When the operation applied to the column is invertible and monotonic — which the four basic arithmetic operators with a constant operand are — you can apply the inverse to the other side instead: ```sql -- non-sargable WHERE price * 1.2 > 120 -- sargable WHERE price > 120 / 1.2 -- best: fold the constant yourself WHERE price > 100 ``` The last form is preferable when the numbers are known at authoring time: it removes any doubt about how the engine evaluates the division and makes the intent of the boundary explicit to the next reader. Date arithmetic is the same transformation with different spelling, and it is by far the most common instance in real code: ```sql -- non-sargable: adds an interval to every stored date WHERE order_date + INTERVAL '30' DAY < CURRENT_DATE -- sargable: one constant computed before the scan WHERE order_date < CURRENT_DATE - INTERVAL '30' DAY ``` Both forms select orders older than thirty days. Only the second gives the index a boundary. Interval literal spelling differs between engines, so use the form yours accepts; the shape is what transfers. ## The traps in the rewrite **Sign flips.** Dividing or multiplying an inequality by a negative constant reverses it. `qty * -2 > 100` is `qty < -50`, not `qty > -50`. Getting this wrong changes the result set, which makes it strictly worse than the slow query you started with. Equality predicates are immune — `col * -2 = 100` is simply `col = -50`. **Integer division.** If the column and the constant are integer types and the engine truncates integer division, the two forms may not be equivalent. Consider `qty / 2 > 100` under truncating integer semantics: `qty = 201` gives `100`, which fails the test, while the naive rewrite `qty > 200` passes it. When the types are integral, derive the boundary deliberately rather than by algebra, and check the rows sitting exactly on it. **Decimal and floating rounding.** `120 / 1.2` may not be representable exactly. For money columns, prefer a boundary you can write exactly, or express the constant so the comparison lands where you intend. A row sitting precisely on the boundary is the one that will differ, and it is the one nobody tests. **NULL is unaffected.** If `price` is NULL, both forms evaluate to unknown and the row is not returned. The rewrite does not change NULL handling, and no `IS NOT NULL` needs adding. ## Where the technique stops Moving arithmetic only works when the other side is free of the column. `WHERE col_a - col_b > 0` can be tidied to `WHERE col_a > col_b`, and that is genuinely better — it removes a computation and reads more clearly — but neither side is a constant, so an index on either column still has no boundary to seek to. Predicates that compare two columns of the same row are not made sargable by algebra; if they are hot, the answer is usually to store the derived value, not to rewrite the comparison. Likewise, when the column is inside a non-invertible expression — a hash, a modulo, a string function — there is nothing to move, and you are in the territory of normalizing the data or indexing the expression instead. ## The habit to take away Read every predicate with one question: *is the column alone on its side?* If not, ask whether the operation applied to it can be undone with constants. If it can, undo it on the other side and check the sign and the arithmetic types. If it cannot, the fix is not in the WHERE clause at all.
- What must you change when the constant multiplier is negative?Flip the comparison operator. `qty * -2 > 100` becomes `qty < -50`, because dividing an inequality by a negative number reverses it. Equality predicates need no flip. Forgetting this turns a slow-but-correct query into a fast-and-wrong one, which is the worse outcome.
- Can WHERE col_a - col_b > 0 be made sargable?Not by algebra. Rewriting it as `col_a > col_b` removes the computation and reads better, but both sides still mention columns of the same row, so no index gets a constant boundary. If the comparison is hot, the usual answer is to persist the derived value and index that instead.
- Does moving arithmetic off the column change how NULLs are handled?No. If the column is NULL, the arithmetic yields NULL and the comparison is unknown in either form, so the row is excluded both ways. The rewrite is result-preserving with respect to NULL, and adding an `IS NOT NULL` guard would be redundant.
saying these in an interview costs you the question
- Keeps the operator unchanged when dividing by a negative constant
- Assumes the optimizer folds price * 1.2 into a range on price
- Rewrites integer division without checking the boundary row
- Claims col_a > col_b is sargable because no function appears
- Thinks the rewrite needs an extra IS NOT NULL check