Predicates That Defeat an Index

Wrapping a column in a function, leading a LIKE with a wildcard, or comparing a string column to a number all disable the index.

SyntaxWHERE indexed_col >= const AND indexed_col < const -- bare column, constant on the other side

An index stores the column's values in order. A predicate can use that order only when the indexed column appears bare on one side, compared against a constant. Anything that transforms the column first makes the stored order irrelevant, and MySQL falls back to reading rows. The index still exists, it still costs you on every write — it just stops being used.

The four common breakers

  • A function or arithmetic on the column. DATE(placed_at) = '2026-03-14' or total_cents / 100 > 50. The index holds placed_at, not DATE(placed_at). Move the transformation to the constant side, or rewrite as a half-open range.
  • A leading wildcard. LIKE '%example.com' has no known prefix to seek to. LIKE 'ada@%' is a normal range scan.
  • An implicit cast. Comparing a string column to a number converts both sides to floating point, so the index is unusable — '0123', ' 123' and '123x' all become 123. Quote the literal.
  • A mismatched collation or character set on a join column, which forces a per-row conversion. Fix the column definition, not the query.

When you cannot rewrite

If the expression is genuinely required, index the expression: a functional key part ((LOWER(email))) from MySQL 8.0.13, or the longhand it is built from — a generated column with an index on it. Either way the query must repeat the expression exactly for the optimizer to match it.

Example

Example · sql
-- KEY idx_placed (placed_at), KEY idx_total (total_cents) on orders

-- Defeated: placed_at is wrapped, so idx_placed cannot be seeked.
SELECT id FROM orders WHERE DATE(placed_at) = '2026-03-14';        -- type: ALL

-- Fixed: a half-open range on the bare column. Identical rows, and it keeps
-- working for DATETIME and TIMESTAMP with sub-second values.
SELECT id FROM orders
WHERE placed_at >= '2026-03-14 00:00:00'
  AND placed_at <  '2026-03-15 00:00:00';                          -- type: range

-- Defeated: arithmetic on the column.
SELECT id FROM orders WHERE total_cents / 100 > 50;
-- Fixed: do the arithmetic on the constant instead.
SELECT id FROM orders WHERE total_cents > 50 * 100;

-- Defeated: a leading wildcard has no prefix to seek to.
SELECT id FROM customers WHERE email LIKE '%@example.com';
-- Fine: a trailing wildcard is an ordinary index range scan.
SELECT id FROM customers WHERE email LIKE 'ada@%';

-- Defeated: the month extracted from the column.
SELECT id FROM orders WHERE YEAR(placed_at) = 2026 AND MONTH(placed_at) = 3;
-- Fixed: one contiguous range the index can walk.
SELECT id FROM orders
WHERE placed_at >= '2026-03-01' AND placed_at < '2026-04-01';

When to use it

  • Rewriting a date-function filter as a range so the existing index is used again.
  • Tracking down why a join between two tables refuses to use either index.
  • Making a case-insensitive email or username lookup indexable.
  • Reviewing an ORM-generated WHERE clause that quietly casts a column.

More examples

The implicit cast, in a WHERE and in a JOIN

The manual is explicit about this one: for comparisons of a string column with a number, MySQL cannot use an index on the column, because many different strings convert to the same number. The same failure hides in joins when two columns disagree on type or collation, and there it is invisible in the query text — you have to look at the schema. Run this check whenever a join is unexpectedly type: ALL.

Example · sql
-- products.sku is VARCHAR(32).
SELECT * FROM products WHERE sku = 12345;     -- both sides -> DOUBLE, full scan
SELECT * FROM products WHERE sku = '12345';   -- index lookup

-- Confirm it. MySQL warns when it coerces a bad string.
SELECT 1 > '6x', 7 > '6x';   -- 0, 1  -- '6x' became 6

-- In a join, the mismatch lives in the schema, not the SQL. Check both sides.
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME IN ('sku', 'product_sku');

-- A utf8mb4_0900_ai_ci column joined to a utf8mb4_general_ci column, or a
-- VARCHAR joined to a BIGINT, converts per row and cannot use the index.
SELECT o.id FROM order_items AS oi
JOIN products AS p ON p.sku = oi.product_sku;

-- Fix the definition, not the query. Do not paper over it with CAST() in the
-- ON clause -- that just moves the function onto the column.
ALTER TABLE order_items
  MODIFY product_sku VARCHAR(32)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

Indexing the expression: shorthand and longhand

When the expression must stay, index it. A functional key part (MySQL 8.0.13 and later) and an indexed virtual generated column are the same mechanism — MySQL implements the functional key part as a hidden virtual generated column. The longhand is worth knowing because the column is selectable and inspectable, which makes debugging a non-matching predicate much easier.

Example · sql
-- Shorthand: functional key part. Note the doubled parentheses -- the
-- expression needs its own, inside the key part list.
ALTER TABLE customers ADD KEY idx_email_lower ((LOWER(email)));

SELECT id FROM customers WHERE LOWER(email) = '[email protected]';   -- ref

-- Longhand: the virtual generated column plus an ordinary index. This is
-- what the functional key part builds internally, but the column is visible
-- and you can SELECT it to see exactly what is stored.
ALTER TABLE customers
  ADD COLUMN email_lower VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(email)) VIRTUAL,
  ADD KEY idx_email_lower2 (email_lower);

SELECT id FROM customers WHERE email_lower = '[email protected]';    -- ref

-- Either way, the predicate must repeat the expression exactly. This does
-- NOT match idx_email_lower:
SELECT id FROM customers WHERE LOWER(TRIM(email)) = '[email protected]';

-- Same technique for a date bucket that you filter on constantly.
ALTER TABLE orders ADD KEY idx_placed_day ((DATE(placed_at)));

OR, and the suffix-search problem

OR across two different columns cannot be satisfied by one composite index. MySQL may rescue it with index_merge, but often does not, so splitting into a UNION gives each branch its own index. The second half handles the leading-wildcard case that genuinely needs suffix matching: store the reversed value and index that, turning a suffix search into a prefix search.

Example · sql
-- One index cannot serve both sides. Check EXPLAIN for index_merge first.
EXPLAIN SELECT id FROM orders
WHERE customer_id = 42 OR reference = 'INV-9001';

-- Split it so each branch uses its own index. UNION (not UNION ALL) keeps
-- the de-duplication that OR gave you.
SELECT id FROM orders WHERE customer_id = 42
UNION
SELECT id FROM orders WHERE reference = 'INV-9001';

-- Suffix search. 'LIKE ''%@example.com''' can never seek, but the reverse of
-- the value can: a suffix becomes a prefix.
ALTER TABLE customers
  ADD COLUMN email_rev VARCHAR(255)
    GENERATED ALWAYS AS (REVERSE(email)) VIRTUAL,
  ADD KEY idx_email_rev (email_rev);

-- 'moc.elpmaxe@%' is a prefix range on idx_email_rev.
SELECT id FROM customers WHERE email_rev LIKE REVERSE('@example.com') || '%';
SELECT id FROM customers WHERE email_rev LIKE CONCAT(REVERSE('@example.com'), '%');

-- For substring search anywhere in a body of text, this is the wrong tool --
-- use a FULLTEXT index and MATCH ... AGAINST instead.

Related

sql explainsql composite indexsql slow queriessql likesql generated columns

Discussion

  • Be the first to comment on this lesson.