Part 4 of 5 · 2 min read
Obfuscated conditions
A condition can be logically equivalent to a sargable one while still being written in a way that defeats index use.
Obfuscated conditions
Think of two directions to the same house. "Turn left at the second street" and "travel in the direction that is not the three other directions available at this intersection" lead to the same house. Only one of them can be followed directly from a map's layout.
In plain words: an obfuscated condition is logically equivalent to some sargable condition, matching exactly the same rows, but is written in a form the optimizer cannot recognize as matching the index's sorted structure. Common examples include negated conditions such as NOT (placed_on < '2026-01-01') instead of placed_on >= '2026-01-01', or arithmetic performed on the indexed column, such as price + 0 = 24.99 instead of price = 24.99. Rewriting an obfuscated condition into its simpler, equivalent, sargable form is often the entire fix needed to let an existing index be used.
A worked example. WHERE price * 1 = 24.99 matches exactly the same rows as WHERE price = 24.99, but the multiplication obfuscates the condition, likely forcing the database to compute price * 1 for every row instead of using an index on price directly.
The key lines.
-- Obfuscated: arithmetic on the indexed column blocks direct index use
SELECT * FROM orders WHERE price * 1 = 24.99;
-- Sargable: the equivalent, simplified condition
SELECT * FROM orders WHERE price = 24.99;
Obfuscated condition
An obfuscated condition is logically equivalent to a sargable condition, matching the same rows, but is written in a form that prevents the optimizer from recognizing it as index-usable.
Example: describing a direction as "not the other three directions available," rather than naming the actual turn, is an obfuscated version of a simple direction.
Remember this
An index can only help a query if the condition is written in a form that matches its sorted structure. Checking a slow query's WHERE clause for a function or arithmetic wrapped around an indexed column, before blaming the index itself, is often the fastest fix available.
Quick notes
- A sargable predicate can be evaluated directly using an index's sorted structure.
- Wrapping an indexed column in a function, such as
YEAR(column), makes a condition non-sargable. - A range-based rewrite often restores sargability for a date-function condition.
- An obfuscated condition matches the same rows as a sargable one, but is written in a way that blocks index use.
- Simplifying arithmetic or negation on an indexed column is often enough to restore index use.