Explain Index Anti-patterns in Databases

3 / 66

indexing

Index Anti-patterns

Recognize patterns that silently disable indexes — implicit casts, leading wildcards, OR conditions, functions on columns.

The index exists, the query is still slow, and nothing reports an error. The result is correct; it just took thirty seconds.

A phone book sorted by last name; if you only know "Sm-something at the start", a wildcard search forces flipping through every page.

Key Concepts

This is nearly always the query being written in a way that makes the index unusable. One rule explains most cases.

Keep the indexed column bare. An index stores the column's values. It does not store the values of expressions you wrap around them.

    WHERE YEAR(created_at) = 2026        -- index unusable
    WHERE created_at >= '2026-01-01'
      AND created_at <  '2027-01-01'     -- same rows, index used

The index holds created_at values, not YEAR(created_at) values, so the first form forces the database to compute the function for every row before it can compare. The second asks for a range of stored values, which is exactly what a sorted index gives you.

created_atYEAR(created_at)

Same problem, different disguises:

    WHERE price * 1.1 > 100              -- arithmetic on the column
    WHERE LOWER(email) = 'a@b.com'       -- function on the column
    WHERE CAST(id AS TEXT) = '42'        -- explicit conversion

If you genuinely query by the expression, build an index on the expression itself.

The invisible cast. Compare an indexed text column to a number and most databases convert the column, not the literal:

    WHERE phone = 9876543210     -- phone is VARCHAR: converts every row
    WHERE phone = '9876543210'   -- index used

Nothing warns you. The types silently disagree and the index silently drops out.

A leading wildcard has nowhere to start. LIKE '%gupta' gives no known prefix, so sorted order is useless and the whole index is read. LIKE 'gupta%' is a range and works fine.

LIKE '%gupta'LIKE 'gupta%'

OR across different columns. WHERE email = ? OR phone = ? usually cannot use one index, since neither one covers both branches. Some databases combine two indexes; many just scan. Writing it as a UNION of two indexable queries often fixes it outright.

ORWHERE email = ? OR phone = ?UNION

Nothing is wrong and the scan is correct. If the predicate matches most of the table, scanning really is faster. Before hunting for a bug, check how many rows the query returns.

How to tell which of these you have. Run EXPLAIN. You are looking for a sequential or full scan where you expected an index seek. The plan says what actually happened; reasoning about the SQL says only what you intended.

EXPLAIN

What the interviewer is probing.1. "Why does WHERE YEAR(created) = 2026 ignore the index?" Probing: the classic. Stalls: "It should use it." Moves up: wrapping the column in a function means the stored values no longer match what is being compared — rewrite as a range on created.

2. "What is wrong with indexing every column?" Probing: the cost. Stalls: "Nothing." *Moves up:* writes update every index, storage grows, and the planner has more options to get wrong — indexes should follow actual queries.

3. "Why is an index on a low-cardinality column often useless?" Probing: selectivity. Stalls: "It still helps." Moves up: a boolean matching half the table means a scan is cheaper than index lookups plus fetches — unless it is part of a composite or a partial index.

4. "What is the problem with a leading wildcard LIKE?" Probing: the ordering. Stalls: "None." Moves up: LIKE '%term' cannot use a B-tree, because the index is ordered by prefix — full-text or trigram indexing is the answer.