Deep Guide to MySQL Index Failure: From Core Mechanics to High‑Concurrency Production Practices

Why Index Failure Crashes Systems

When an order‑lookup API that normally responds in 5‑15 ms suddenly takes seconds under load, teams often first consider scaling or caching, but the root cause is frequently an index path that the optimizer has abandoned.

Impact Across the Stack

Database layer: random I/O, amplified back‑table lookups, reduced Buffer Pool hit rate.

Application layer: connection pool exhaustion, thread blocking.

Cache layer: cache miss storms flood the database.

Architecture layer: added machines provide diminishing returns, leading to uncontrolled slowdown.

Four Core Questions the Article Answers

Why can MySQL ignore an existing index?

Which query patterns turn an index lookup into a full scan?

Why manual experience alone cannot solve index problems in high‑concurrency systems?

How to turn index design, inspection, changes, and rollbacks into an engineering loop?

Understanding InnoDB Indexes

Clustered vs. Secondary Indexes

Clustered index leaf nodes store the whole row; secondary index leaf nodes store only the indexed column values plus the primary key. This means a secondary index may still require a costly back‑table lookup.

Why B+Tree Is Fast

Ordered keys allow rapid range narrowing.

Low height (3‑4 levels for millions of rows).

Page‑based storage limits I/O.

Optimizer Decision Process

The optimizer estimates the cost of a full table scan versus an index scan plus back‑table, sorting, temporary tables, or joins. If the index path appears more expensive, it is discarded.

Four Index‑Failure States

type = ALL : full table scan.

type = index with high rows: inefficient index scan.

type = ref/range with high rows: severe back‑table cost.

optimizer mis‑prediction : better index exists but statistics or cost model are wrong.

Why the Optimizer Gives Up

Non‑prunable Access Paths

Functions on indexed columns (e.g., DATE(created_at)).

Expressions or arithmetic on indexed columns.

Skipping the leftmost prefix of a composite index.

Leading wildcard LIKE '%xxx' patterns.

Negated conditions ( !=, NOT IN, IS NOT NULL).

Stale or Skewed Statistics

Data distribution changes, missing histograms, or delayed ANALYZE TABLE can cause the optimizer to mis‑estimate row counts and choose a full scan.

When Back‑Table Is More Expensive Than a Scan

On a 10 M‑row table where 85 % of rows have status = 1, scanning the secondary index would retrieve 8.5 M primary keys, leading to 8.5 M random I/O operations—often slower than a sequential scan.

ORDER BY / GROUP BY Mismatch

If the sort order does not match the index order, MySQL must use Using filesort, negating the index benefit.

12 High‑Frequency Failure Scenarios

1. Implicit Type Conversion

CREATE TABLE orders (
  id BIGINT NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no)
) ENGINE=InnoDB;

-- Wrong: numeric parameter on VARCHAR column
SELECT * FROM orders WHERE order_no = 202406291234567890;

-- Correct: string literal
SELECT * FROM orders WHERE order_no = '202406291234567890';

Solution: enforce matching types in the application layer and DTO definitions.

2. Function on Indexed Column

-- Bad
SELECT * FROM user_login_log WHERE DATE(created_at) = '2026-06-29';

-- Good
SELECT * FROM user_login_log WHERE created_at >= '2026-06-29 00:00:00' AND created_at < '2026-06-30 00:00:00';

Consider adding a generated column or a functional index (MySQL 8.0.13+).

3. Arithmetic on Indexed Column

-- Bad
SELECT * FROM orders WHERE user_id + 1 = 10001;

-- Good
SELECT * FROM orders WHERE user_id = 10000;

4. Composite Index Missing Leftmost Prefix

KEY idx_user_status_created (user_id, status, created_at);

-- Uses only user_id
SELECT * FROM orders WHERE user_id = 1001;

-- Uses user_id and status
SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

-- Cannot use index for status only
SELECT * FROM orders WHERE status = 1;

Design index column order based on query patterns.

5. Range Condition Cuts Off Later Columns

SELECT * FROM orders WHERE user_id > 1000 AND status = 1 AND created_at >= '2026-06-01';

After a range on user_id, the optimizer cannot use ordering on status or created_at for index seeks.

6. Leading Wildcard LIKE

-- Index usable
SELECT * FROM product WHERE sku LIKE 'SKU2026%';

-- Index not usable
SELECT * FROM product WHERE sku LIKE '%2026%';

Use a search engine for arbitrary substring searches.

7. OR Conditions

SELECT * FROM orders WHERE user_id = 1001 OR status = 1;

MySQL may use index_merge, which often performs worse than separate queries.

8. Negated Conditions

SELECT * FROM orders WHERE status <> 1;
SELECT * FROM orders WHERE status NOT IN (3,4,5);
SELECT * FROM orders WHERE deleted_at IS NOT NULL;

Prefer positive enumeration or redesign the schema to avoid such filters.

9. Low‑Cardinality Index on High‑Selectivity Query

EXPLAIN SELECT * FROM orders WHERE status = 1;

If status = 1 matches >70 % of rows, the index provides little benefit.

10. ORDER BY / GROUP BY Mismatch

KEY idx_user_status_created (user_id, status, created_at);

EXPLAIN SELECT id, created_at FROM orders WHERE status = 1 ORDER BY created_at DESC LIMIT 20;

Consider reordering the index to (status, created_at, id) for this workload.

11. Join Column Type Mismatch

CREATE TABLE user_account (user_no VARCHAR(32) NOT NULL, ...);
CREATE TABLE user_profile (user_no BIGINT NOT NULL, ...);

-- Join forces implicit conversion, preventing index usage
SELECT a.id, p.nickname FROM user_account a JOIN user_profile p ON a.user_no = p.user_no;

Ensure joined columns share type, charset, and collation.

12. Stale Statistics / Data Skew

Production data can differ dramatically from test data, causing previously fast queries to become scans. Regular ANALYZE TABLE, histograms, and partitioning mitigate this.

Production Diagnosis Workflow

Step 1: Examine EXPLAIN

Focus on type, key, rows, filtered, and Extra fields.

Step 2: Use EXPLAIN ANALYZE (MySQL 8.0+)

Shows actual execution time and row counts, revealing estimation errors.

Step 3: Enable optimizer_trace

SET optimizer_trace = 'enabled=on';
SELECT id, order_no FROM orders WHERE user_id = 1001 AND status = 1 ORDER BY created_at DESC LIMIT 20;
SELECT * FROM information_schema.OPTIMIZER_TRACE\G

Shows candidate indexes and why a particular one was rejected.

Step 4: Correlate with performance_schema

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT, SUM_ROWS_EXAMINED FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;
SELECT * FROM sys.schema_tables_with_full_table_scans;
SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

Identify high‑cost SQL fingerprints, tables with frequent full scans, and unused or redundant indexes.

Step 5: Map Slow SQL Back to Business Entry

Determine which API or background job generates the query to prioritize remediation.

Engineering Index Governance

Design Phase

Answer four questions for each high‑frequency query: equality vs. range filters, sorting/grouping needs, covering columns, and QPS/latency impact.

Follow the column‑order principle: high‑frequency equality columns → high‑selectivity columns → range columns → sorting columns → covering columns.

Avoid “one‑size‑all” mega‑indexes; they increase write amplification and cache pressure.

Covering Indexes

Use them for small result‑set list pages, but avoid overly wide covering indexes that hurt writes.

Large Pagination

-- Bad: deep offset
SELECT id, order_no, created_at FROM orders WHERE user_id = 1001 ORDER BY created_at DESC LIMIT 100000, 20;

-- Better: use cursor or bookmark pagination
SELECT id, order_no, created_at FROM orders WHERE user_id = 1001 AND created_at < '2026-06-29 18:30:00' ORDER BY created_at DESC LIMIT 20;

Online DDL

Use tools like gh-ost or pt-online-schema-change with load thresholds and graceful rollback.

gh-ost \
  --host=mysql-primary \
  --database=order_db \
  --table=orders \
  --alter="ADD INDEX idx_status_created(status, created_at)" \
  --max-load=Threads_running=32 \
  --critical-load=Threads_running=64 \
  --chunk-size=1000 \
  --execute

Platform‑Level Automation

Monitor rows_examined/rows_sent, full‑scan rate, index hit ratio, Buffer Pool hit rate, connection pool wait time, and replication lag.

Maintain an audit ledger for critical SQL (owner, interface, execution plan, P99 latency, scan rows, associated indexes).

Static code scans for risky patterns (leading wildcard LIKE, SELECT *, OR, NOT IN, IS NOT NULL) using scripts like the Python example below.

#!/usr/bin/env python3
import pathlib, re, sys
RISK_RULES = [
    ("leading wildcard", re.compile(r"like\s+'%[^']*'", re.IGNORECASE)),
    ("select all", re.compile(r"select\s+\*", re.IGNORECASE)),
    ("negative filter", re.compile(r"\b(not\s+in|is\s+not\s+null|!=|<>)\b", re.IGNORECASE)),
    ("or condition", re.compile(r"\bor\b", re.IGNORECASE)),
]
def scan_file(path: pathlib.Path):
    content = path.read_text(encoding="utf-8", errors="ignore")
    findings = []
    for name, pat in RISK_RULES:
        for m in pat.finditer(content):
            line = content[:m.start()].count('
') + 1
            findings.append(f"{path}:{line}:{name}")
    return findings
if __name__ == "__main__":
    root = pathlib.Path(sys.argv[1] if len(sys.argv) > 1 else ".")
    files = [p for p in root.rglob("*") if p.suffix in {".sql", ".xml", ".java", ".kt"}]
    all_findings = []
    for f in files:
        all_findings.extend(scan_file(f))
    if all_findings:
        print("Potential SQL risks found:")
        for i in all_findings:
            print(i)
        sys.exit(1)
    print("No obvious SQL risk patterns found.")
    sys.exit(0)

MyBatis Interceptor for Runtime Risk Detection

package com.example.order.infra.mybatis;

import java.util.Locale;
import java.util.concurrent.TimeUnit;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

@Component
@Intercepts({
    @Signature(
        type = Executor.class,
        method = "query",
        args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
    )
})
public class SqlRiskInspectorInterceptor implements Interceptor {
    private static final Logger log = LoggerFactory.getLogger(SqlRiskInspectorInterceptor.class);
    private static final long SLOW_SQL_THRESHOLD_MS = 80L;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement statement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        BoundSql boundSql = statement.getBoundSql(parameter);
        String normalizedSql = normalize(boundSql.getSql());
        detectRisk(statement, normalizedSql);
        long start = System.nanoTime();
        try {
            return invocation.proceed();
        } finally {
            long elapsedMs = TimeUnit.NANOSECONDS.toMillis(System.nanoTime() - start);
            if (statement.getSqlCommandType() == SqlCommandType.SELECT && elapsedMs >= SLOW_SQL_THRESHOLD_MS) {
                log.warn("slow_sql mappedStatementId={}, elapsedMs={}, sql={}", statement.getId(), elapsedMs, normalizedSql);
            }
        }
    }

    private void detectRisk(MappedStatement statement, String sql) {
        if (!sql.startsWith("select")) return;
        if (sql.contains(" like '%")) {
            log.warn("sql_risk like_leading_wildcard mappedStatementId={}, sql={}", statement.getId(), sql);
        }
        if (sql.contains(" select * ")) {
            log.warn("sql_risk select_all mappedStatementId={}, sql={}", statement.getId(), sql);
        }
        if (sql.contains(" or ")) {
            log.info("sql_risk or_condition mappedStatementId={}, sql={}", statement.getId(), sql);
        }
        if (sql.contains(" is not null") || sql.contains(" not in ")) {
            log.info("sql_risk negative_filter mappedStatementId={}, sql={}", statement.getId(), sql);
        }
    }

    private String normalize(String sql) {
        return " " + sql.replaceAll("\\s+", " ").trim().toLowerCase(Locale.ROOT) + " ";
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
}

Checklist for Production Index Governance

Design Phase

Identify core high‑frequency query paths.

Design indexes based on access patterns, not field intuition.

Separate primary‑DB and replica‑DB index responsibilities.

Evaluate write amplification and index size.

Avoid low‑cardinality single‑column indexes.

Development Phase

Ensure DTO types match column types.

Avoid SELECT * in production code.

Never apply functions or implicit casts on indexed columns.

Steer clear of high‑frequency OR, NOT IN, IS NOT NULL patterns.

Validate execution plans for pagination, sorting, and joins.

Testing Phase

Run EXPLAIN ANALYZE on production‑scale data.

Verify slow‑SQL amplification under concurrency.

Test data skew, hot‑cold mixes, and tenant‑level skew.

Release Phase

Apply online DDL for index changes.

Assess replica lag and metadata lock impact.

Define change windows, circuit‑breaker thresholds, and rollback plans.

Capture pre‑ and post‑change execution plans.

Runtime Phase

Continuously monitor full‑scan rate and SQL fingerprint cost.

Periodically drop redundant or unused indexes.

Refresh statistics and watch plan drift.

Trace slow SQL back to owning service and owner.