Skip to content

CONCEPT Cited by 3 sources

Index obfuscation

Index obfuscation is the situation where a query filters on an indexed column but wraps the column in an expression (function call, arithmetic, implicit cast) so the planner no longer recognises the predicate as index-eligible. The index still exists, the query still returns correct results — but the planner silently falls back to a full table scan because the expression's value is not the thing the index is sorted by.

Aaron Francis's folk-framing:

When we wrap the created_at column in a function, we're obfuscating the column from MySQL, and it can no longer use the index. By wrapping the created_at column in a YEAR function, we're asking MySQL to do an index lookup on YEAR(created_at), which is not an index MySQL maintains. It is only maintaining the created_at index.

— Aaron Francis, Using redundant conditions to unlock indexes in MySQL

(Source: sources/2026-04-21-planetscale-using-redundant-conditions-to-unlock-indexes-in-mysql.)

Industry-standard term: non-sargable

The database-systems term of art is non-sargable predicate — "Search ARGument ABLE". A sargable predicate is one the planner can push down to an index seek; a non-sargable predicate forces a full scan because the indexed column is hidden behind an expression. Francis's "obfuscation" framing is more accessible to developers who don't know the optimiser term of art, and is the framing this wiki canonicalises.

The four classes

Index obfuscation divides into four common classes:

  1. Function wrapping — WHERE YEAR(created_at) = 2023 when the index is on created_at. The fix is either a range rewrite (when the function is invertible on the interval of interest — BETWEEN '2023-01-01' AND '2023-12-31') or a functional index on the expression itself.
  2. Implicit type cast — WHERE string_col = 123 when the column is VARCHAR. MySQL casts every row's string to an integer before comparison and the index becomes unusable. Canonical fix: quote the literal (= '123'). This is the subtlest class because the query returns correct results and only EXPLAIN reveals the scan.
  3. Arithmetic on the indexed column — WHERE col + 1 = x rather than WHERE col = x - 1. The first form is non-sargable, the second is a normal index seek.
  4. Multi-column expressions — WHERE ADDTIME(due_date, due_time) BETWEEN ... AND ... where the expression spans two columns. No single-column index can serve this predicate; the fix is either a multi-column functional index or a redundant condition on a projection of the expression.

Canonical EXPLAIN signature

Index obfuscation has an unambiguous EXPLAIN fingerprint. The sargable version produces:

type  : range | ref
key   : <index_name>
rows  : small
Extra : Using index condition

The obfuscated version — same index, same data, same semantic predicate — produces:

type  : ALL
key   : NULL
rows  : <row count of the table>
Extra : Using where

The telltale three-column flip (type: ALL, key: NULL, rows: table_cardinality) is the primary operational diagnostic. See concepts/mysql-access-type for the full access-type ladder; ALL is the worst rung.

Three escape hatches

In order of preference:

  1. Rewrite the predicate to be sargable. For invertible functions this is a mechanical rewrite: YEAR(c) = 2023 → c BETWEEN '2023-01-01' AND '2023-12-31'; col + 1 = x → col = x - 1; 'a' || col LIKE 'a%' → col LIKE '%'. Always the first choice when available.
  2. Add a functional index on the expression (MySQL 8.0.13+) — or an explicit named generated column indexed separately. Trades write-amplification budget for planner visibility.
  3. Add a redundant condition on a single-column projection of the wrapped expression. Costs no schema change; costs some false-positive candidate rows that the original predicate filters out. The right tool when the function wraps multiple columns (cases #2 and #4 above) or when adding an index is blocked by write-amplification budget.

Why it matters

Index obfuscation is a silent-failure class. The query returns correct results and the app behaves normally — the symptom is a "slow query" alarm or a "database is slower than last week" operational complaint. Without EXPLAIN, the cause is invisible. Francis's framing makes this legible to developers who would otherwise never suspect their innocuous YEAR() call was the reason their todos table went from 1ms queries to 40ms queries when row count crossed 10k.

The class is easy to introduce and hard to detect in review: WHERE DATE(created_at) = '2023-06-07' looks natural; WHERE created_at >= '2023-06-07' AND created_at < '2023-06-08' looks awkward. Teams that don't teach sargability end up with this pattern spread across their application code.

Implicit type cast: the silent footgun

Of the four classes, implicit type cast is the hardest to spot because there is no visible function call. MySQL's rule for mixed-type comparisons is: convert strings to numbers. So:

-- id is INT, query passes it as a string
WHERE id = '123'  -- sargable (literal cast to int)

-- phone is VARCHAR, query passes it as a number
WHERE phone = 5551234  -- NON-sargable (column cast to number)

The second form silently full-scans because every phone row value must be cast to a number before comparison. The fix is to quote the literal. Production incidents from this class have been reported across ORMs that auto-infer bind parameter types from language values (PHP's loose typing, Ruby's to_s coercions, JavaScript's Number/String ambiguity).

Relationship to redundant conditions

Redundant conditions are the compensating pattern for index obfuscation when neither rewrite nor functional index is available. The redundant condition re-expresses a subset of the query's filter using only indexed columns without expressions; the planner uses the index to narrow the candidate set; the expensive expression then filters out false positives post-index.

The two canonical uses on the wiki:

Seen in

Last updated · 470 distilled / 1,213 read