10 Common MySQL Index Failure Scenarios – Which Ones Affect You?

Problem Background

Even when an index exists, queries can still run slowly because the optimizer chooses a full‑table scan (type=ALL) or ignores the index. The article collects the ten most common scenarios that cause index loss and, for each, describes the phenomenon, the underlying principle, reproducible SQL, verification steps, and concrete fix methods.

Scenario 1 – Functions or Operations on Indexed Columns

Applying functions (e.g., YEAR(), DATE(), LEFT(), SUBSTRING()) or arithmetic operations ( +, -, *, /) to an indexed column forces MySQL to evaluate the expression row‑by‑row, preventing index condition pushdown and resulting in a full scan.

SELECT * FROM t_order_demo WHERE YEAR(create_time) = 2026;

Fix: rewrite the predicate to a range condition that keeps the column in its original form, e.g.,

SELECT * FROM t_order_demo WHERE create_time >= '2026-01-01 00:00:00' AND create_time < '2027-01-01 00:00:00';

Scenario 2 – Implicit Type Conversion

When a column type (e.g., VARCHAR or BIGINT) is compared with a literal of a different type, MySQL casts the column values for each row, which disables the index.

SELECT * FROM t_order_demo WHERE user_id = '12345';  -- slow

Fix: ensure the parameter type matches the column type, e.g., use a numeric literal or cast explicitly.

SELECT * FROM t_order_demo WHERE user_id = 12345;  -- fast

Scenario 3 – LIKE with Leading Wildcard

A pattern that starts with '%' (e.g., LIKE '%abc' or LIKE '%abc%') removes the leftmost prefix, so the B‑tree cannot determine a start point and must scan the whole index.

SELECT * FROM t_order_demo WHERE order_no LIKE '%00012345%';

Fixes include using a trailing wildcard ( LIKE 'abc%'), creating a reversed column with an index, or employing a full‑text index.

Scenario 4 – OR Conditions Missing an Indexed Column

If an OR expression contains one side without an index, MySQL often prefers a full scan because mixing indexed and non‑indexed parts would require two passes.

SELECT * FROM t_order_demo WHERE user_id = 1 OR status = 2;

Fixes: add an index to the missing column, or rewrite the query as a UNION of two indexed selects.

Scenario 5 – Violating the Left‑Most Prefix Rule of Composite Indexes

A composite index (A,B,C) can be used only if the query predicates start with column A and continue consecutively. Skipping A or using only C makes the index unusable.

-- slow, uses only status (second column)
SELECT * FROM t_order_demo WHERE status = 2;

Fix: either add a single‑column index for the needed column or redesign the composite index to match the query pattern.

Scenario 6 – Small Tables Prefer Full Scan

For tables with only a few hundred rows, the optimizer may deem a full scan cheaper than an index lookup because it avoids random I/O.

SELECT * FROM t_order_demo WHERE user_id = 1;  -- type=ALL

Usually no action is required; if a forced index is needed, use USE INDEX(idx_user_id) or FORCE INDEX.

Scenario 7 – Stale Statistics

Incorrect row‑count estimates (e.g., rows column in EXPLAIN) cause the optimizer to choose a sub‑optimal plan. ANALYZE TABLE t_order_demo; After updating statistics, the plan should reflect the actual cardinality.

Scenario 8 – IS NULL / IS NOT NULL

Indexes do not store NULL values, so queries that filter on IS NULL or IS NOT NULL may trigger a full scan.

SELECT * FROM t_order_demo WHERE amount IS NOT NULL AND amount <> 0;

Replace with a concrete range condition or store a default non‑null value.

Scenario 9 – GROUP BY / DISTINCT Without Index

When the grouping column lacks an index, MySQL must sort the result set, creating a temporary table ( Using temporary; Using filesort).

SELECT status, COUNT(*) FROM t_order_demo GROUP BY status;

Fix: add an index on the grouping column or use a covering index that includes the selected columns.

Scenario 10 – ORDER BY Not Covered by Index

ORDER BY can use an index only if the ordering columns appear in the index in the same order and direction. Otherwise MySQL adds Using filesort.

SELECT * FROM t_order_demo WHERE user_id = 1 ORDER BY status;

Fix: create an index that matches the WHERE and ORDER BY columns, ensure consistent ASC/DESC direction, or use a covering index.

Comprehensive Checklist

When an index appears ineffective, follow this order: check EXPLAIN type and key, verify rows estimate, examine WHERE for functions, type casts, LIKE patterns, OR usage, left‑most prefix, review ORDER BY/GROUP BY, consider table size, refresh statistics, and handle NULL conditions.

Index Design Principles

Build indexes driven by actual query patterns.

Limit the number of indexes per table (5‑6 is a practical ceiling).

Place high‑cardinality columns first in composite indexes.

Prefer covering indexes to avoid back‑table lookups.

Eliminate redundant indexes using performance_schema.table_io_waits_summary_by_index_usage.

Production‑Ready Index Management

Before deployment, run EXPLAIN on slow queries, confirm the expected index is used, ensure no Using filesort, and benchmark execution time. Monitor index usage via the performance schema, and when removing an index, first make it INVISIBLE, observe stability, then drop it.

Interview Questions

Typical interview topics include reasons for index loss, distinguishing index problems from configuration issues, when to use covering indexes, why the optimizer may pick a wrong index, and how to apply optimizer hints responsibly.

Conclusion

Index loss is not a defect in the index itself but a mismatch between query predicates and index structure. By understanding the ten failure patterns, applying the suggested fixes, and following disciplined index design and maintenance practices, developers can keep MySQL queries fast and predictable.