PATTERN Cited by 1 source

De-obfuscate via redundant condition¶

Pattern: when a query's WHERE clause wraps an indexed column in an expression that defeats the index (see index obfuscation), and you cannot rewrite the predicate to be sargable, add a redundant predicate on an indexed single-column projection of the expression, chosen to be broader than the real predicate so it cannot exclude a row the real predicate would include. The planner uses the redundant predicate to narrow the row set via index; the original non-sargable predicate then filters out false positives.

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

The three-step decision tree¶

Encountered a query where EXPLAIN shows type: ALL / key: NULL despite an index existing on a column referenced in WHERE? Walk the tree:

Can you rewrite the predicate to be sargable? If the function wrapping the indexed column is invertible on the interval of interest, rewrite to a range predicate:

-- Obfuscated
WHERE YEAR(created_at) = 2023

-- Sargable rewrite
WHERE created_at >= '2023-01-01 00:00:00'
  AND created_at <  '2024-01-01 00:00:00'

This is the first-choice fix. Applies to YEAR(), DATE(), MONTH(), arithmetic (col + 1 = x → col = x - 1), and string prefix (LEFT(col, 3) = 'abc' → col LIKE 'abc%' when collation permits).

Does the expression wrap a single column in a way that can be bounded by another predicate on that column? If not — typically when the expression spans multiple columns — add a logically-redundant condition on the indexed column alone:

-- Real predicate spans two columns
WHERE ADDTIME(due_date, due_time)
      BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
  -- Logically-redundant condition on due_date alone
  AND due_date BETWEEN CURRENT_DATE
                   AND CURRENT_DATE + INTERVAL 1 DAY

The due_date range is 2 days (today + tomorrow) to cover the 1-day real window's midnight crossing. The due_date predicate cannot exclude any row the ADDTIME predicate would include: for any due_time, a due-datetime between NOW() and NOW() + 1 day must have a due_date somewhere in [CURRENT_DATE, CURRENT_DATE + 1 day].

This is the canonical Francis 2023 pattern.

Is there an application invariant linking the unindexed column to an indexed one? If not — typically when querying on a non-indexed column that has a known relationship to an indexed one — add a domain-knowledge redundant condition:

-- Query is on unindexed updated_at
WHERE updated_at < '2023-01-01 00:00:00'
  -- Domain-knowledge redundant: app maintains
  -- created_at <= updated_at for all rows
  AND created_at < '2023-01-01 00:00:00'

This is the most powerful but most fragile tier. The invariant must hold forever under all writes the app performs — see concepts/domain-knowledge-redundant-condition for the failure-mode enumeration.

EXPLAIN before and after¶

The pattern's success is verifiable in EXPLAIN:

Before (obfuscated):

type  : ALL
key   : NULL
rows  : 39,746
Extra : Using where

After (redundant condition added):

type  : range
key   : due_date
rows  : 1
Extra : Using index condition; Using where

The Using where in the Extra column is the signal that the expensive non-sargable predicate is still evaluated after the index narrows the set — confirmation that the redundant condition is a planner hint, not a semantic change.

The broadness requirement¶

The redundant condition must be strictly broader than the real predicate. If it is narrower — even by a small margin — it silently excludes rows the real predicate would include, corrupting the result set without raising an error.

	Real predicate	Redundant predicate	Outcome
✅	`BETWEEN NOW() AND NOW()+1d`	`due_date BETWEEN today AND today+1d`	Superset; always safe
⚠	`BETWEEN NOW() AND NOW()+1d`	`due_date = CURRENT_DATE`	Wrong: drops rows due_date=tomorrow+before-midnight
⚠	`YEAR(c) = 2023`	`c BETWEEN '2023-01-01' AND '2023-12-31 23:59:59'`	Maybe wrong: drops rows at `2023-12-31 23:59:59.5` under fractional-seconds schema
✅	`YEAR(c) = 2023`	`c >= '2023-01-01' AND c < '2024-01-01'`	Half-open; always safe

Francis's own example commits the fractional-seconds bug (BETWEEN '2023-01-01 00:00:00' AND '2023-12-31 23:59:59') — safe on 1-second TIMESTAMP columns, unsafe on TIMESTAMP(6). The half-open-interval form is strictly safer.

Composition with existing indexing patterns¶

The pattern composes with existing wiki index-design guidance:

patterns/composite-index-for-and-predicate — when you can add an index and the query is stable, a composite index is usually the correct fix. Redundant conditions are the fallback when an index can't be added or the query is ad-hoc.
patterns/generated-hash-column-for-equality-lookup — for equality lookup on long unindexable columns (URLs, blobs), the redundant-condition pattern composes with a generated hash column: WHERE url_md5 = UNHEX(MD5(?)) AND url = ?. See patterns/redundant-hash-plus-value-predicate.
Functional index — for function-wrapped predicates that recur, a functional index is stronger than a redundant condition because it doesn't require the rewrite to be present in every query occurrence. Trade-offs: functional index costs write-amplification budget; redundant condition costs zero.

When not to use¶

The query is run millions of times per day. A functional index or proper single-column index pays back its write-amplification cost quickly under high query frequency. Reserve redundant conditions for rarely-run queries where schema change is disproportionate.
The application invariant is not clearly documented. Domain-knowledge redundant conditions depend on invariants that must be maintained globally across all writers. If the invariant is not written down and enforced (ideally as a CHECK constraint), the redundant condition will rot silently.
The redundant condition's broadness admits too many false positives. The post-index filter on the original predicate still runs; if the broader redundant condition admits 10× more rows than the real predicate, the post-filter cost may outweigh the index savings. Measure via EXPLAIN ANALYZE or actual timing.

The trade-off summary¶

Approach	Schema change	Correctness risk	Works for ad-hoc	Best for
Sargable rewrite	None	None	Yes	Invertible single-column functions
Add index on target column	ALTER TABLE	None	No	Frequent queries on the same column
Functional index	ALTER TABLE	None	No	Frequent queries with same expression
Logical redundant condition	None	None	Yes	Multi-column expressions with a bounding single-column projection
Domain-knowledge redundant condition	None	App-invariant-dependent	Yes	Unindexed columns with known-correlated indexed columns

Seen in¶

sources/2026-04-21-planetscale-using-redundant-conditions-to-unlock-indexes-in-mysql — canonical wiki introduction. Aaron Francis walks both the logical and domain-knowledge flavours on a synthetic todos table.