Skip to content

CONCEPT Cited by 1 source

Redundant condition (query hint)

A redundant condition is a WHERE clause predicate that is logically redundant — it does not change the result set — but is added deliberately so that the query planner can pick a different, usually index-assisted, execution plan. The pattern is PlanetScale's framing: "A redundant condition is a condition that does not change the results of the query but does provide MySQL with the option to choose a different execution path, usually an index-assisted path."

(Source: sources/2026-04-21-planetscale-generated-hash-columns-in-mysql.)

Canonical instance: hash + value

The canonical deployment on the wiki is the paired predicate for hash-collision safety against a generated hash column:

SELECT * FROM visits
  WHERE
    url_md5 = UNHEX(MD5('https://planetscale.com'))  -- index-eligible
    AND
    url = 'https://planetscale.com';                 -- eliminates collisions
  • The first predicate is the index-steering condition — url_md5 carries the B-tree index, so this lets the planner use the index to narrow the row set.
  • The second predicate is the correctness guard — in the vanishingly rare case that two distinct URLs hash to the same MD5, the second predicate re-filters those few candidate rows against the original column.

Neither predicate alone does both jobs: the hash condition alone admits collision false-positives; the url = ? condition alone has no index to use (per BLOB/TEXT prefix requirement) and forces a full-table scan on a TEXT column.

See patterns/redundant-hash-plus-value-predicate for the pattern canonicalisation.

Relationship to index hints

A redundant condition is a soft steering mechanism — the planner may use the hinted path, not must. Stronger alternatives:

  • USE INDEX (idx) / FORCE INDEX (idx) — MySQL hint syntax that directly names the desired index; less portable across engines.
  • Rewriting the query so only the index-eligible predicate is present (sacrificing collision safety).

Aaron Francis's framing prefers redundant conditions because they are portable SQL (no engine-specific hint syntax) and fail-safe (if the planner ignores the hint, the query still returns correct results, just slower).

Generalisation beyond hashing

The pattern isn't unique to hashing. Any time a query has a cheap-but-approximate predicate on an indexed column alongside an expensive-but-exact predicate on an unindexed column, pairing them as redundant conditions steers the planner through the cheap path while retaining exactness:

  • Range predicates on a timestamp column alongside application-semantic filters that don't reduce to SQL cleanly.
  • Bloom-filter-style pre-filter columns (boolean "might match" flags) paired with the exact predicate they approximate.
  • Shard-locality predicates (WHERE shard_key = ?) added to queries that would otherwise fan out to every shard.

Seen in

Last updated · 347 distilled / 1,201 read