Predicate Anti-Patterns

## Why the leading % is the problem A B-tree index stores values in sorted order, and a seek works by narrowing to a range of that order. `LIKE 'abc%'` is a range: every match sorts between `abc` and the next value after the `abc` prefix. `LIKE '%abc'` is not: matching values are scattered throughout the sort order, because sorting is by first character and the pattern says nothing about the first character. With no prefix to anchor on, the predicate can only be checked value by value. So the rewrite goal is precise and mechanical: **manufacture a known prefix**. ## Rewrite 1 — store what you actually search for The strongest fix is usually schema, not syntax. If the application searches by email domain, the domain is a real attribute of the row and deserves a column: ```sql ALTER TABLE users ADD COLUMN domain VARCHAR(255); -- populated on write, or as a generated column where the engine supports it CREATE INDEX idx_users_domain ON users (domain); SELECT id, email FROM users WHERE domain = 'example.com'; ``` This is equality on a bare column: the most index-friendly predicate there is. It also makes the intent readable, allows a composite index with other filters, and lets you count domains, group by them, and constrain them. Generated columns are standard SQL, though the exact syntax and whether the value is stored or computed on read varies by engine; a plain column maintained by the application is the fully portable version. ## Rewrite 2 — the reversed column When the search is genuinely "ends with" and you cannot decompose the value, exploit a small identity: a suffix of a string is a prefix of the reversed string. Keep a reversed copy of the column, index it, and reverse the search literal when you build the query: ```sql -- email_reversed holds 'moc.elpmaxe@ecila' for '[email protected]' CREATE INDEX idx_users_email_rev ON users (email_reversed); SELECT id, email FROM users WHERE email_reversed LIKE 'moc.elpmaxe@%'; ``` The pattern now has a known prefix, so a range seek is possible. Points to raise unprompted: - The SQL standard does not define a `REVERSE` function; several engines provide one, so maintaining the column may be the application's job for full portability. - The reversed value must be kept in sync on every write. A generated column does that for you where available; otherwise a write path can drift, and drift here means silently missing rows. - The literal must be reversed by whatever builds the query, which is easy to get wrong by hand and belongs in one helper. - It costs storage and index maintenance on a second copy of the data. ## Rewrite 3 — check the pattern was needed at all A surprising share of leading wildcards are habit. A "search by name" box implemented as `LIKE '%' || :term || '%'` often only ever needed "starts with". If the product requirement allows it, `LIKE :term || '%'` is seekable exactly as written, with no schema change and no trick. Ask before you engineer. ## What none of these fix Infix search — `LIKE '%example%'` — has no anchor at either end, so neither the original nor the reversed column offers a prefix. Reversing helps suffixes only. Genuine substring search over text is what engines' dedicated search facilities exist for, and choosing among those is an engine-specific decision outside the portable-SQL toolbox. Say that plainly rather than pretending a B-tree trick covers it. A related non-fix: adding `DISTINCT`, changing `LIKE` to a comparison operator, or reordering the `WHERE` clause. None of them create a prefix. ## Choosing between them Ask what the search means. If the suffix corresponds to a real concept — domain, file extension, country code at the end of a code, area code — model it as a column; you will want to filter, group and constrain on it eventually anyway. If it is an arbitrary tail of an opaque string and the table is large enough that a scan hurts, the reversed column earns its keep. If the table is small, or the query runs once a day in a report, do nothing: an anti-pattern that costs 30 milliseconds on 20,000 rows is not worth a second copy of a column. ## In an interview State the mechanism in one sentence (a leading wildcard leaves no prefix to seek), then give the two rewrites in order of preference, then the honest limit: reversing handles suffixes, not infixes. Volunteering the maintenance cost of the reversed column, and the question of whether the leading `%` was ever required, is what distinguishes an engineer from someone reciting a trick.