Skip to content

CONCEPT Cited by 1 source

Wildcard-prefix non-sargable predicate

A wildcard-prefix LIKE predicate — a LIKE pattern that begins with % or _ (e.g. '%ron', '%oh%') — cannot use a B+tree index, so a WHERE name LIKE '%ron' query falls back to a full table scan even when a name index exists. This is structural, not a planner heuristic: the index's sort order cannot answer suffix queries.

(Source: sources/2026-04-21-planetscale-why-isnt-mysql-using-my-index.)

The one-line rule

MySQL can use the index until it reaches the first wildcard character. The index is not considered if the search string starts with a wildcard character.

— Aaron Francis, Why isn't MySQL using my index?

A wildcard at the start of the pattern is disqualifying. A wildcard anywhere after the first literal character still permits index use (as a range scan up to the wildcard, then a filter pass on the candidates).

Why the B+tree can't answer suffix queries

A secondary B+tree on a string column is sorted lexicographically by prefix — all entries starting with 'Aa' are adjacent, all starting with 'Ab' are adjacent, and so on. Navigating the tree requires knowing the leading characters to pick the right child page.

  • LIKE 'Aa%' supplies the leading 'Aa' → the planner seeks to that subtree and walks it in order. Worked EXPLAIN: type: range, rows: 356.
  • LIKE '%ron' supplies no leading characters → no seek is possible. The planner cannot pick a starting page. It falls back to a full table scan. Worked EXPLAIN: type: ALL, rows: 493,889.

On Aaron Francis's 491,583-row table, the difference is ~1,387× more estimated rows examined — the difference between a ~millisecond-class lookup and a whole-table scan.

type: range vs type: ALL — the EXPLAIN signature

EXPLAIN SELECT * FROM people WHERE first_name LIKE 'Aa%';
-- type: range, key: first_name, rows: 356, Extra: Using index condition

EXPLAIN SELECT * FROM people WHERE first_name LIKE '%ron';
-- type: ALL, key: NULL, rows: 493889, Extra: Using where

When troubleshooting a slow LIKE query, the EXPLAIN access type is the first signal: range means you're using the index up to the wildcard; ALL means you've paid for a table scan.

The non-sargable vocabulary

Non-sargable (coined from "search argumentable") is the general term for any predicate the planner cannot translate into a direct B+tree seek. Wildcard-prefix LIKE is one class of non-sargable predicate; the other canonical class is index obfuscation (wrapping an indexed column in a function). Both produce the same type: ALL EXPLAIN signature for the same structural reason: the B+tree is not keyed on what the predicate is asking about.

When suffix / substring search is required

For robust suffix or substring search at scale, the MySQL toolbox offers:

  • Fulltext indexes — inverted-index structure over word tokens; answer MATCH(name) AGAINST ('ron') queries in sub-linear time on token membership, not B+tree prefix.
  • Reversed-column index — store REVERSE(name) in a generated column and index it; suffix search becomes prefix search on the reversed column. "LIKE '%ron'" → "LIKE 'nor%' on reverse_name".
  • External search engine — Elasticsearch, OpenSearch, Typesense for full-text + substring + fuzzy search patterns the RDBMS doesn't model natively.
  • Trigram indexes — PostgreSQL's pg_trgm extension indexes 3-character substrings; MySQL has no native equivalent but the functional-index-on-generated-column pattern can approximate.

Francis notes this is beyond his post's scope but points at PlanetScale's indexing for wildcard searches and fulltext indexes courses.

Seen in

Last updated · 470 distilled / 1,213 read