10 Common MySQL Index Failure Scenarios and How to Fix Them

Overview

MySQL slow queries are one of the most frequent performance bottlenecks in production. Even with carefully designed indexes, queries may remain slow because the optimizer cannot use the index. Understanding the typical reasons for index loss and mastering concrete solutions is essential for backend engineers and DBAs.

Technical Characteristics

Characteristic 1: Index loss is usually caused by SQL syntax, data‑type conversion, or function usage rather than the index itself.

Characteristic 2: The optimizer may choose completely different execution plans for the same query under different data volumes or distributions.

Characteristic 3: Grasping MySQL’s cost‑based optimizer is key to predicting and solving index‑loss problems.

Applicable Scenarios

Production environment raises slow‑query alerts and needs rapid root‑cause analysis.

Code‑review discovers potential index‑loss risks that should be refactored early.

During database schema design, avoid common index‑loss pitfalls.

Environment Requirements

Component: MySQL 5.7+/8.0+ (article uses MySQL 8.0).

Permission: SELECT/EXPLAIN privileges.

Tools: pt‑query‑digest (optional), Prometheus + Grafana for monitoring.

Detailed Steps

Preparation

Enable slow‑query log (dynamic settings):

-- View current slow‑query settings
SHOW VARIABLES LIKE '%slow_query%';

-- Enable slow‑query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;  -- record queries >1 s
SET GLOBAL log_queries_not_using_indexes = 'ON';

Permanent configuration (my.cnf):

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

Prepare test data (1 M rows):

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  age INT DEFAULT NULL,
  status TINYINT DEFAULT '1',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_username (username),
  KEY idx_email (email),
  KEY idx_age (age),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DELIMITER $$
CREATE PROCEDURE generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000000 DO
    INSERT INTO users (username, email, age, status, created_at)
    VALUES (
      CONCAT('user', i),
      CONCAT('user', i, '@example.com'),
      FLOOR(RAND()*60)+18,
      FLOOR(RAND()*3),
      DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND()*1460) DAY)
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL generate_test_data();

EXPLAIN Basics

EXPLAIN SELECT * FROM users WHERE age = 25;
-- Important fields:
-- type: connection type (ALL < index < range < ref < eq_ref < const)
-- possible_keys: indexes that could be used
-- key: index actually used
-- key_len: length of the index used
-- rows: estimated rows examined
-- Extra: additional info (Using where, Using index, Using filesort, etc.)

Ten Index‑Loss Scenarios

Scenario 1 – Functions on Indexed Columns

Problem:

EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2023;

Result: type=ALL, key=NULL, rows=1 000 000 → index not used.

Cause: MySQL cannot use an index when a function is applied to the indexed column.

Solution:

-- Rewrite condition
SELECT * FROM users WHERE created_at >= '2023-01-01' AND created_at < '2024-01-01';

-- Or create a functional index (MySQL 8.0+)
ALTER TABLE users ADD INDEX idx_created_year ((YEAR(created_at)));
EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2023;

Scenario 2 – Implicit Type Conversion

Problem:

EXPLAIN SELECT * FROM users WHERE username = 123456;

Result: type=ALL → full table scan.

Cause: MySQL casts the string column to a number (CAST(username AS SIGNED)), which disables the index.

Solution:

EXPLAIN SELECT * FROM users WHERE username = '123456';

Scenario 3 – LIKE with Leading Wildcard

Problem:

EXPLAIN SELECT * FROM users WHERE username LIKE '%admin%';

Result: type=ALL → index not used.

Cause: B‑tree indexes are ordered left‑to‑right; a leading wildcard prevents range lookup.

Solution:

-- Suffix wildcard can use index
EXPLAIN SELECT * FROM users WHERE username LIKE 'admin%';

-- For true prefix search, use FULLTEXT index or external search engine
ALTER TABLE users ADD FULLTEXT INDEX ft_username (username);
SELECT * FROM users WHERE MATCH(username) AGAINST('admin' IN NATURAL LANGUAGE MODE);

Scenario 4 – OR Condition with Unindexed Column

Problem:

EXPLAIN SELECT * FROM users WHERE status = 1 OR created_at > '2023-01-01';

Result: type=ALL → full scan.

Cause: MySQL can use index_merge only if every OR branch can use an index.

Solution:

-- Rewrite as UNION
SELECT * FROM users WHERE status = 1
UNION
SELECT * FROM users WHERE created_at > '2023-01-01';

-- Or add index on created_at
ALTER TABLE users ADD INDEX idx_created_at (created_at);

Scenario 5 – Composite Index Not Following Left‑Prefix Rule

Problem:

EXPLAIN SELECT * FROM users WHERE created_at > '2023-01-01';
-- Composite index (status, created_at) exists but not used

Cause: The leftmost column (status) is missing in the predicate.

Solution:

-- Adjust query order
EXPLAIN SELECT * FROM users WHERE status = 1 AND created_at > '2023-01-01';

-- Or create a single‑column index on created_at
ALTER TABLE users ADD INDEX idx_created_at (created_at);

Scenario 6 – NOT EQUAL (!= or <>)

Problem:

EXPLAIN SELECT * FROM users WHERE status != 1;

Result: type=ALL or range depending on data distribution.

Cause: Inequality often requires scanning a large portion of the table; optimizer may prefer full scan.

Solution:

-- Use IN for known small set
SELECT * FROM users WHERE status IN (0,2);

-- Create covering index to reduce back‑row lookups
ALTER TABLE users ADD INDEX idx_status_id (status, id);
EXPLAIN SELECT id FROM users WHERE status != 1;

Scenario 7 – IS NULL / IS NOT NULL

MySQL can store NULL in indexes. IS NULL usually uses the index; IS NOT NULL may not if most rows are NOT NULL.

Solution: Avoid NULL when possible, or use a sentinel value and index it.

Scenario 8 – Range Query Followed by Additional Columns

Problem:

EXPLAIN SELECT * FROM users WHERE status = 1 AND age > 25 AND created_at > '2023-01-01';

Cause: After a range condition (age > 25), subsequent columns (created_at) cannot use the index.

Solution:

-- Put range column last in the composite index
ALTER TABLE users DROP INDEX idx_status_age_created;
ALTER TABLE users ADD INDEX idx_status_created_age (status, created_at, age);

Scenario 9 – Skewed Data Distribution

When a value appears in >30 % of rows, the optimizer may choose a full scan.

Solution: Use covering indexes, force index (cautiously), or partition the table.

Scenario 10 – Mismatched Collation or Character Set

Different collation in query vs. index can prevent index usage.

Solution:

-- Align collation
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Or create a collation‑specific index
ALTER TABLE users ADD INDEX idx_username_bin (username) COLLATE utf8mb4_bin;

Example Scripts and Configurations

Slow‑Query Analysis Script (bash)

#!/bin/bash
SLOW_LOG="/var/log/mysql/slow.log"
REPORT="/tmp/slow_query_report_$(date +%Y%m%d).txt"

echo "========== MySQL Slow‑Query Analysis Report ==========" > "$REPORT"
echo "Generated at: $(date '+%Y-%m-%d %H:%M:%S')" >> "$REPORT"

if command -v pt-query-digest >/dev/null; then
  echo "【TOP 10 Slow Queries】" >> "$REPORT"
  pt-query-digest "$SLOW_LOG" --limit 10 >> "$REPORT"
else
  echo "pt-query-digest not installed, using mysqldumpslow" >> "$REPORT"
  mysqldumpslow -s t -t 10 "$SLOW_LOG" >> "$REPORT"
fi

echo -e "
【Queries Not Using Indexes】" >> "$REPORT"
grep "Query_time" "$SLOW_LOG" | head -20 >> "$REPORT"

echo "Analysis complete, report saved to $REPORT"

Index Usage Monitoring View

CREATE OR REPLACE VIEW v_slow_queries AS
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT/1000000000,2) AS avg_time_ms,
  ROUND(MAX_TIMER_WAIT/1000000000,2) AS max_time_ms,
  ROUND(SUM_ROWS_EXAMINED/COUNT_STAR,0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_database'
  AND AVG_TIMER_WAIT > 1000000000
GROUP BY DIGEST_TEXT
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

Best Practices and Cautions

Index Design Principles

Principle 1: Prioritize columns with high selectivity.

Principle 2: Composite index order: equality columns → range columns → order‑by columns.

Principle 3: Avoid redundant indexes; keep the minimal set.

SQL Writing Guidelines

Avoid SELECT *; specify needed columns to enable covering indexes.

Never apply functions or calculations to indexed columns in the WHERE clause.

Prefer UNION ALL over UNION when duplicate elimination is unnecessary.

Regular Maintenance Tasks

Update statistics regularly: ANALYZE TABLE users; Clean up table fragmentation: OPTIMIZE TABLE users; Monitor slow‑query count via

SHOW GLOBAL STATUS LIKE 'Slow_queries';

Common Pitfalls

Too many indexes degrade write performance; keep indexes ≤ 5 per table.

Incorrect column order in composite indexes renders later columns unusable.

Small tables may not need indexes now, but plan ahead for growth.

Performance Testing Tips

Use realistic data volume (≥ 1 M rows) and distribution.

Clear caches before each run (e.g., RESET QUERY CACHE; or restart InnoDB buffer pool).

Leverage sysbench or custom stored procedures to compare original vs. optimized queries.

Troubleshooting and Monitoring

Slow‑Query Diagnosis Flow (bash)

#!/bin/bash
# 1. Verify slow‑query log is enabled
mysql -e "SHOW VARIABLES LIKE '%slow_query%';"
# 2. List currently running long queries
mysql -e "SELECT * FROM information_schema.processlist WHERE time > 5 ORDER BY time DESC;"
# 3. Examine EXPLAIN plan for a suspect query
mysql -e "EXPLAIN SELECT * FROM users WHERE username = 'test'\G"
# 4. Show index information
mysql -e "SHOW INDEX FROM users;"
# 5. Check table statistics
mysql -e "SHOW TABLE STATUS LIKE 'users'\G"

Common Issues

EXPLAIN shows index but query is still slow: likely many back‑row lookups; create a covering index.

Same SQL uses index in test but not in production: different data distribution or stale statistics; run ANALYZE TABLE on production.

Adding indexes slows down writes: reduce redundant indexes or create them after bulk inserts.

Real‑Time Monitoring View (SQL)

CREATE OR REPLACE VIEW v_slow_queries AS
SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT/1000000000,2) AS avg_time_ms,
  ROUND(MAX_TIMER_WAIT/1000000000,2) AS max_time_ms,
  ROUND(SUM_ROWS_EXAMINED/COUNT_STAR,0) AS avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'your_database'
  AND AVG_TIMER_WAIT > 1000000000
GROUP BY DIGEST_TEXT
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

Prometheus & Grafana Alerts

# prometheus.yml snippet for MySQL exporter
scrape_configs:
- job_name: 'mysql'
  static_configs:
  - targets: ['localhost:9104']

# Alert rule for high slow‑query rate
- alert: HighSlowQueries
  expr: rate(mysql_global_status_slow_queries[5m]) > 10
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "MySQL slow queries exceed threshold"
    description: "Instance {{ $labels.instance }} slow‑query rate: {{ $value }}/s"

Key Monitoring Metrics

Slow queries (< 1 % of total queries is healthy).

QPS – watch for sudden drops (> 50 % decrease).

Index hit rate (> 95 % desirable).

InnoDB row‑lock wait count (< 10 / s).

Conclusion

Index loss occurs when the optimizer cannot map the query to the B‑tree structure; typical causes include functions, implicit casts, leading wildcards, OR conditions, left‑prefix violations, range queries, data skew, and collation mismatches.

Design composite indexes following equality → range → order‑by order, keep selectivity high, and avoid redundant indexes.

Regularly update statistics, monitor key performance counters, and use the provided scripts to automate analysis.

Balance read‑performance gains against write‑overhead; do not over‑index.

Further Learning

Deep dive into MySQL storage engine and optimizer internals (e.g., "MySQL Technical Internals" and "High Performance MySQL").

Explore sharding, read/write splitting, and proxy solutions (ShardingSphere, Vitess, ProxySQL).

Study NewSQL databases (TiDB, CockroachDB, YugabyteDB) for distributed indexing strategies.

References

MySQL Official Documentation – Optimization.

"High Performance MySQL".

Percona Blog – MySQL performance tuning.

pt‑query‑digest tool.