Why Is Your SQL Slow? 7 Common MySQL Index Failure Scenarios Explained

Overview

Slow query optimization, index design, SQL auditing, and database performance tuning are the primary use cases for this guide, which targets MySQL 5.7+ or 8.0+ (InnoDB) environments with root or DBA privileges and production tables exceeding one million rows.

MySQL Query Execution Flow

SQL Statement
   ↓
Connector (permission check)
   ↓
Query cache (removed in MySQL 8.0)
   ↓
Parser (lexical/semantic analysis)
   ↓
Optimizer (choose index, generate execution plan) ← index failure occurs here
   ↓
Executor (call storage engine)
   ↓
Storage engine (InnoDB: read B+Tree index or full table scan)
   ↓
Result set

Index Usage Decision Process

Optimizer analyzes WHERE clause
   ↓
Is there a usable index?
   ├─ Yes → Is the index selective?
   │        ├─ High selectivity (>30%) → Index Scan
   │        └─ Low selectivity (<30%) → Table Scan
   └─ No → Table Scan

Key Components

B+Tree Index : InnoDB default; leaf nodes store full rows (clustered) or primary keys (secondary).

EXPLAIN : Shows whether a query uses an index, scanned rows, and index type.

Index Selectivity : COUNT(DISTINCT column) / COUNT(*); values close to 1 indicate good selectivity.

Covering Index Lookup : Secondary index finds primary key, then fetches full row.

Step 1 – Environment Preparation & Test Data

Goal: Create a test table with indexes and insert 1 000 000 rows.

# Connect to MySQL
mysql -u root -p
# Create database
CREATE DATABASE IF NOT EXISTS test_db DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE test_db;
# Create test table
DROP TABLE IF EXISTS user_orders;
CREATE TABLE user_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT 'primary key',
  user_id INT UNSIGNED NOT NULL COMMENT 'user ID',
  order_no VARCHAR(32) NOT NULL COMMENT 'order number',
  order_status TINYINT NOT NULL DEFAULT 0 COMMENT 'order status: 0‑4',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'order amount',
  create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'update time',
  INDEX idx_user_id (user_id),
  INDEX idx_order_status (order_status),
  INDEX idx_create_time (create_time),
  INDEX idx_amount (amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='user orders';
# Insert data via stored procedure (≈1 M rows)
DELIMITER $$
DROP PROCEDURE IF EXISTS generate_test_data$$
CREATE PROCEDURE generate_test_data()
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 1000000 DO
    INSERT INTO user_orders (user_id, order_no, order_status, amount, create_time)
    VALUES (
      FLOOR(1 + RAND() * 100000),
      CONCAT('ORD', LPAD(i,10,'0')),
      FLOOR(RAND() * 5),
      ROUND(RAND() * 10000,2),
      DATE_ADD('2023-01-01', INTERVAL FLOOR(RAND()*365) DAY)
    );
    SET i = i + 1;
    IF i % 10000 = 0 THEN COMMIT; END IF;
  END WHILE;
  COMMIT;
END$$
DELIMITER ;
CALL generate_test_data();

Step 2 – Scenario 1: Functions or Expressions on Indexed Columns

Problem: Using functions (e.g., YEAR(create_time)) or arithmetic on indexed columns prevents index usage.

-- Bad example (index fails)
EXPLAIN SELECT * FROM user_orders WHERE YEAR(create_time) = 2023;
-- Expected: type=ALL, key=NULL (full scan)

-- Good example (use range on raw column)
EXPLAIN SELECT * FROM user_orders WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
-- Expected: type=range, key=idx_create_time

Performance Test : Function‑based query takes ~0.85 s, range query ~0.05 s → 17× faster.

Step 3 – Scenario 2: Implicit Type Conversion

Problem: Mismatched column and literal types trigger hidden conversions, causing the optimizer to ignore indexes.

-- Example: VARCHAR column compared to numeric literal
EXPLAIN SELECT * FROM user_orders WHERE user_id = 12345; -- user_id is INT, but stored as VARCHAR → type=ALL
-- Fix: Use matching types
EXPLAIN SELECT * FROM user_orders WHERE user_id = 'U12345';

Key Rules :

String → number conversion disables index.

Number → string may use index in newer versions.

Different character sets also break index usage.

Step 4 – Scenario 3: OR Conditions

Problem: Columns combined with OR where not all have indexes lead to full scans.

-- Bad: one column lacks index
EXPLAIN SELECT * FROM user_orders WHERE user_id = 12345 OR order_no = 'ORD0000012345';
-- Good: rewrite as UNION or create composite index
EXPLAIN SELECT * FROM user_orders WHERE user_id = 12345 UNION SELECT * FROM user_orders WHERE order_no = 'ORD0000012345';

Performance: OR query ~0.75 s, UNION version ~0.12 s.

Step 5 – Scenario 4: LIKE with Leading Wildcard

Problem: Patterns like LIKE '%keyword' or LIKE '%keyword%' cannot use B+Tree indexes.

-- Bad
EXPLAIN SELECT * FROM user_orders WHERE order_no LIKE '%12345';
-- Good (prefix)
EXPLAIN SELECT * FROM user_orders WHERE order_no LIKE 'ORD%';
-- Even better: full‑text index or external search engine

Performance: prefix LIKE ~0.002 s vs leading wildcard ~0.95 s → 475× faster.

Step 6 – Scenario 5: Negative Conditions (!=, NOT IN, IS NOT NULL)

Problem: Negative predicates often return a large portion of rows, making full scans cheaper.

-- Bad
EXPLAIN SELECT * FROM user_orders WHERE order_status != 1;
-- Good (rewrite as positive IN)
EXPLAIN SELECT * FROM user_orders WHERE order_status IN (0,2,3,4);

Performance: full scan ~0.68 s, IN rewrite ~0.15 s → 4.5× faster.

Step 7 – Scenario 6: Composite Index Not Following Left‑most Prefix

Problem: Skipping the first column of a composite index prevents its use.

-- Bad: skip user_id
EXPLAIN SELECT * FROM user_orders WHERE order_status = 1 AND create_time > '2023-06-01';
-- Good: include first column
EXPLAIN SELECT * FROM user_orders WHERE user_id = 12345 AND order_status = 1 AND create_time > '2023-06-01';

Performance: violating prefix ~0.72 s, obeying prefix ~0.003 s → 240× faster.

Step 8 – Scenario 7: NULL Checks and Arithmetic on Indexed Columns

Problem: IS NULL/IS NOT NULL on NOT‑NULL columns or arithmetic on indexed columns disables index usage.

-- Bad arithmetic
EXPLAIN SELECT * FROM user_orders WHERE amount * 1.1 > 5000;
-- Good (move calculation to constant side)
EXPLAIN SELECT * FROM user_orders WHERE amount > 4545.45;

Fundamental Principle

MySQL’s B+Tree indexes are ordered left‑to‑right byte‑wise. Any operation that breaks this order (functions, type casts, leading wildcards, or skipping the leftmost column of a composite index) forces the optimizer to fall back to a full table scan.

Monitoring & Alerting

Enable the slow‑query log with log_queries_not_using_indexes = ON to capture index‑misses. Use mysqldumpslow for analysis, and integrate mysqld_exporter with Prometheus to monitor metrics such as mysql_global_status_slow_queries and mysql_global_status_select_scan. Sample alert rules are provided for high slow‑query rates and excessive full‑table scans.

Common Faults & Troubleshooting

Slow queries (>5 s): run EXPLAIN, add missing indexes.

type=ALL: verify index existence, create if absent.

key=NULL: check for implicit conversions, rewrite SQL.

Large rows scanned: low selectivity, consider FORCE INDEX or redesign.

Extra: Using filesort: add sorting column index.

Slow‑query log spikes: batch‑optimize offending statements.

CPU 100 %: kill long‑running scans, improve indexing.

Change Management & Rollback

Apply index changes first on a replica using ALGORITHM=INPLACE, LOCK=NONE, validate with EXPLAIN, then promote to the primary during low‑traffic windows. Rollback by dropping the new index if performance degrades or space usage exceeds thresholds.

Best Practices

Avoid functions or expressions on indexed columns.

Match data types exactly between columns and literals.

Design composite indexes following the left‑most prefix rule; place high‑selectivity equality columns first.

Replace OR with IN or UNION when possible.

Prefer covering indexes over SELECT * to eliminate back‑table lookups.

Run ANALYZE TABLE regularly and monitor index fragmentation.

FAQ

Q1: How to tell if an index is effective?

Run EXPLAIN and look for type=const/eq_ref/ref and a non‑NULL key. type=ALL indicates a full scan.

Q2: Are more indexes always better?

No. Excessive indexes increase storage and write overhead. Keep ≤5 indexes per table and ≤3 columns per composite index.

Q3: How to find unused indexes?

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 AND object_schema = 'test_db' AND index_name != 'PRIMARY';

Q4: Why does EXPLAIN show type=index ?

It means the optimizer scans the entire index because the WHERE clause cannot use a more selective path.

Q5: How to add indexes to large tables without locking?

ALTER TABLE large_table ADD INDEX idx_col (col) ALGORITHM=INPLACE, LOCK=NONE;

Q6: Choosing column order for composite indexes?

Equality columns first, then range columns.

Higher selectivity columns first.

Columns most frequently used in queries first.

Q7: Optimizing JOINs?

Ensure join columns have indexes on both sides and that filter columns are indexed. Prefer the smaller table as the driving side.

Appendix Scripts

Script 1 – Index Health Check

#!/bin/bash
set -euo pipefail
MYSQL_USER="root"
MYSQL_PASS="yourpassword"
MYSQL_HOST="localhost"
MYSQL_DB="test_db"
# 1. Duplicate indexes
mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "SELECT table_name, GROUP_CONCAT(index_name) AS duplicate_indexes, GROUP_CONCAT(column_name) AS columns FROM information_schema.statistics WHERE table_schema='$MYSQL_DB' GROUP BY table_name, column_name HAVING COUNT(*)>1;"
# 2. Unused indexes (requires performance_schema)
mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star=0 AND object_schema='$MYSQL_DB' AND index_name!='PRIMARY';"
# 3. Low‑selectivity indexes (<10%)
mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "SELECT table_name, index_name, cardinality, (SELECT table_rows FROM information_schema.tables WHERE table_schema=s.table_schema AND table_name=s.table_name) AS rows, ROUND(cardinality/rows*100,2) AS sel_pct FROM information_schema.statistics s WHERE table_schema='$MYSQL_DB' AND index_name!='PRIMARY' HAVING sel_pct<10;"
# 4. Fragmented tables (>20% free space)
mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "SELECT table_name, ROUND(data_length/1024/1024,2) AS data_mb, ROUND(data_free/1024/1024,2) AS free_mb, ROUND(data_free/data_length*100,2) AS frag_pct FROM information_schema.tables WHERE table_schema='$MYSQL_DB' AND data_free>0 HAVING frag_pct>20;"
# 5. Tables with many indexes (>5)
mysql -u "$MYSQL_USER" -p"$MYSQL_PASS" -h "$MYSQL_HOST" -D "$MYSQL_DB" -e "SELECT table_name, COUNT(DISTINCT index_name) AS idx_cnt FROM information_schema.statistics WHERE table_schema='$MYSQL_DB' GROUP BY table_name HAVING idx_cnt>5;"

Script 2 – Slow‑Query Analyzer

#!/bin/bash
set -euo pipefail
SLOW_LOG="/var/log/mysql/slow.log"
OUTPUT="/tmp/slow_query_summary.txt"
# Summarize top 10 slow queries
mysqldumpslow -s t -t 10 "$SLOW_LOG" > "$OUTPUT"
cat "$OUTPUT"
# Total slow queries count
TOTAL=$(grep -c "Query_time" "$SLOW_LOG" || echo 0)
echo "Total slow queries: $TOTAL"
# Show 5 longest SELECT statements
echo "========== Longest 5 SELECTs =========="
grep -A5 "Query_time" "$SLOW_LOG" | grep "SELECT" | head -5