MySQL Slow Query Analysis & Execution Plan Deep Dive: From Cost Model to Production Fixes
A comprehensive technical guide dissecting MySQL slow query root causes through execution plan analysis, covering optimizer cost models, index internals, statistics sampling flaws, 8.4 default changes, and three real-world failure cases with step-by-step forensic scripts and rollback strategies.
Part 1: Where Slow Queries Are Slow — Optimizer Calculates Cost, Not Time
1.1 Three Components of Query_time
Slow log Query_time mixes three distinct phases:
Parse/Optimize — usually <1 ms (unless thousands of partitions)
Execution — InnoDB reads, optimizable via indexes
Wait — lock / IO / network, can consume 100%; adding indexes is useless here
Example: a 12 s query with 11.8 s in innodb_row_lock means plan optimization is irrelevant — shorten the locking transaction instead.
1.2 InnoDB B+ Tree & Lookup Cost
Clustered index stores full rows; secondary index stores (index_cols, PK). Fetching other columns requires a lookup (random PK access). Cost is not in count but randomness: 5,000 sequential index reads (µs) vs 5,000 random PK lookups (random IO if not in buffer pool).
Rule of thumb: when lookup rows exceed 5–10% of table, full scan may beat secondary index + lookups. Derived from cost formula:
Secondary index cost = index_pages × page_cost + lookup_rows × (1 - cache_hit) × random_read_cost
Full scan cost = clustered_pages × page_costWith default page_cost:random_read_cost ≈ 1:4, crossover at 10–20% selectivity.
1.3 Selectivity Estimation via Sampling
Optimizer uses innodb_stats_persistent_sample_pages (default 20) random leaf pages. On a 10 M row table, 20 pages ≈ 2,000 rows (0.02% sample). Skewed data (e.g., status 99% 'done') easily misestimates rare values, causing plan flips. "Yesterday fast, today slow" often means stats were recalculated, not data volume changed.
1.4 MySQL 8.4 Default Changes Affecting Plans innodb_adaptive_hash_index: 8.0 default ON → 8.4 default OFF . Point-lookup QPS may drop 10–20% on memory-rich OLTP; re-enable with innodb_adaptive_hash_index_parts=16. innodb_change_buffering: 8.0 default all → 8.4 default none . Secondary index write amplification increases. mysql_native_password: 8.0 enabled → 8.4 disabled . Old clients fail to connect.
1.5 Production Baseline
32C/256 GB/NVMe RAID1, MySQL 8.4.12, 160 GB buffer pool (62% RAM), orders 42 M rows/24 GB, order_item 110 M rows. 8.0 EOL 2026-04-21; new projects should not baseline on 8.0.
Part 2: Slow Log & Execution Plan Collection Baseline
2.1 Slow Log Config — Coupled Parameters
slow_query_log=ON
slow_query_log_file=/var/log/mysql/slow.log # must be on separate mount
long_query_time=0.5 # default 10 s too coarse
log_queries_not_using_indexes=OFF # ON floods log with small-table scans
log_slow_admin_statements=ON
log_slow_replica_statements=OFF # ON records replica replay as slow
log_slow_extra=ON # critical: adds Rows_examined, Rows_sent, Read_rnd_next, Sort_merge_passes
min_examined_row_limit=100 # filters trivial scansKey couplings: long_query_time + min_examined_row_limit must be set together; log_slow_extra without it only tells "slow", not "why". Log rotation via logrotate with postrotate flush-logs (not restart).
2.2 InnoDB Core Parameters
innodb_buffer_pool_size=160G # hot data × 1.2, ≤75% RAM
innodb_buffer_pool_instances=16 # each ≥1 GB
innodb_redo_log_capacity=16G # replaces innodb_log_file_size in 8.4
innodb_io_capacity=8000 # ≈ disk random write IOPS × 50%
innodb_io_capacity_max=16000
innodb_flush_neighbors=0 # SSD must be 0
innodb_flush_method=O_DIRECT # bypass OS page cache
innodb_stats_persistent_sample_pages=20 # raise to 100–200 for >100 M rows
innodb_stats_auto_recalc=ONHealth threshold: buffer pool hit rate >99% (
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1%). Below 95% means hot data doesn't fit — index tuning is palliative.
2.3 OS Tuning
vm.swappiness=1 # swap causes 200 ms+ spikes
vm.dirty_ratio=15 # default 20 = 51 GB dirty on 256 GB → sec-level stall
vm.dirty_background_ratio=5
net.ipv4.tcp_tw_reuse=1 # prevents TIME_WAIT port exhaustionFile limits via systemd override.conf ( LimitNOFILE=65535), not limits.conf.
2.4 Verification Commands
Check running values:
SELECT @@innodb_buffer_pool_size/1024/1024/1024, @@innodb_redo_log_capacity/1024/1024/1024, @@innodb_io_capacity, @@long_query_time, @@log_slow_extraBuffer pool hit rate:
1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requestsHot tables in buffer pool: sys.innodb_buffer_stats_by_table Top cumulative-time digests:
SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest WHERE SUM_TIMER_WAIT > 1e12(7 digests → optimize these first)
2.5 performance_schema as Full-Census Complement events_statements_summary_by_digest aggregates all executions (not sampled). Example query shows:
1.
SELECT ... WHERE user_id=? AND status=? ORDER BY created_at LIMIT 20— 8,421 execs, avg 219 ms, scans 4.2 M rows, returns 20 → extreme scan/return ratio (210k:1)
2. UPDATE — 1,204 execs, avg 758 ms, scans 1 row → lock wait or flush, not scan
3. COUNT(*) — 88 execs, avg 4.5 s, scans 42 M rows → periodic heavy job
Prioritize by total_time × execs × unit_cost, not just avg.
Part 3: Reading Execution Plans — Which EXPLAIN Fields to Trust
3.1 Four EXPLAIN Variants EXPLAIN — Estimated rows, access type; no execution; quick triage EXPLAIN ANALYZE — Actual rows, actual time; runs query ; find estimation errors optimizer_trace — Candidate plans & choices; session only; why this plan? EXPLAIN FORMAT=TREE — Tree structure; no execution; join order
Critical: EXPLAIN rows = estimate; EXPLAIN ANALYZE actual rows = reality. Order-of-magnitude gap = stats problem.
3.2 type Hierarchy (best → worst):
system → const → eq_ref → ref → range → index → ALL. OLTP standard: const/eq_ref optimal, ref/range acceptable, index = full index scan (still bad on large tables), ALL on 10 M+ tables must fix.
Common trap: type=index looks like "used index" but means full index scan — still O(N). True index use is ref or range.
3.3 Joint Reading of key, rows, filtered
Example:
WHERE user_id=1001 AND status='pending' ORDER BY created_at LIMIT 20key=idx_user_id, type=ref, rows=4820, filtered=10%, Extra=Using filesortReal cost: scan 4,820 secondary index entries → 4,820 random lookups → sort 482 rows → return 20. Bottleneck is lookups (190 ms of 218 ms per EXPLAIN ANALYZE), not sort.
If forced to idx_status_created (status, created_at): rows=420,000 (looks worse) but ordered, stops after 20 rows → 3.2 ms (68× faster). Large ordered rows beat small unordered rows.
3.4 Extra Field Triage
Must fix: Using filesort (large result), Using temporary (spills to disk if > tmp_table_size), Using join buffer (hash join) (no index on driven table)
Context-dependent: Using index (covering = good; but with type=index still full scan), Using index condition (ICP = good), Using where (meaningless alone, pair with filtered)
Ignore: Select tables optimized away, Using index for group-by 3.5 EXPLAIN ANALYZE (8.3+ JSON) shows per-operator actual time/rows. In the example, innermost Index lookup took 189.7 ms for 4,820 rows (accurate estimate), filter added 21 ms, sort 7 ms. 87% time in lookups — contradicts Using filesort hint.
3.6 optimizer_trace reveals why optimizer rejected a seemingly better index. Must run in single session (heredoc or chained -e), increase optimizer_trace_max_mem_size. Trace shows considered_execution_plans with costs: idx_status_created cost 423k (scans 421k rows) vs idx_user_id cost 4,838 (scans 4,820). Optimizer missed that LIMIT 20 on ordered index stops early — rows_estimation phase doesn't model early termination well.
3.7 FORCE INDEX Boundaries
OK: stable schema, fixed query pattern, trace proves optimizer error
Avoid: shifting data distribution, DDL churn, ORM-generated SQL
Better: redesign index (e.g., covering index (user_id, status, created_at, amount) → Using index, 0.9 ms, zero lookups). Trade ~40% index size for stability.
Part 4: High-Frequency Forensic Commands
4.1 Slow Log Aggregation mysqldumpslow -s t -t 10 (built-in, sorts by total time)
pt-query-digest --since '24h' --order-by Query_time:sum --limit 10(adds variance/mean ratio V/M: <1 = steady slow, >2 = sporadic → suspect locks/buffer pool/stats)
4.2 One-Command Scene Capture SHOW FULL PROCESSLIST → Time: 47 (running 47 s vs 218 ms avg) + State: Sending data (misleading: means reading/processing rows, not network) performance_schema.events_waits_current joined with threads → EVENT_NAME distinguishes:
- wait/io/table/sql/handler = storage engine IO (not lock)
- wait/io/file/innodb/innodb_data_file = disk read
- wait/synch/cond/sql/MDL_context::COND_wait_status = metadata lock
4.3 Lock Waits — UPDATE Slowness Root Cause sys.innodb_lock_waits shows:
- wait_age_secs: 107 - blocking_query: NULL (blocker idle but transaction open — classic "app forgot to commit")
- blocking_trx_age: 3:12, blocking_trx_rows_modified: 4,820 Fix: KILL blocking_thread, but rollback of 4,820 rows may take minutes; monitor trx_state: ROLLING BACK + decreasing trx_rows_modified. Do not re-kill or restart.
4.4 Unused Index Detection
performance_schema.table_io_waits_summary_by_index_usage WHERE COUNT_STAR=0 AND COUNT_WRITE > 0→ idx_status maintained 84 M writes, zero reads. Drop after observing full business cycle (7 days) since stats reset on restart.
4.5 Table/Index Size Ratio idx_ratio = INDEX_LENGTH / DATA_LENGTH. Healthy 0.2–0.5; order_item at 0.84 (11 secondary indexes) signals write amplification.
4.6 PromQL Alerts
Slow query rate: rate(mysql_global_status_slow_queries[5m]) > 5 Full scan rate: rate(select_full_join + select_full_range_join[5m]) > 1 (catches missing join indexes before they hit slow log)
Row lock wait: rate(innodb_row_lock_time[5m])/1000 > 1 Buffer pool hit rate:
1 - rate(innodb_buffer_pool_reads)/rate(innodb_buffer_pool_read_requests) < 0.99Temp table disk spill:
rate(created_tmp_disk_tables)/rate(created_tmp_tables) > 0.25Part 5: Three Deep-Dive Failure Loops
5.1 Same SQL, 30 ms → 4.2 s Overnight (No Code/Schema Change)
P99 jumped from 0.031 to 4.18 s instantly — plan change, not data growth. EXPLAIN showed type=ALL despite idx_created_at in possible_keys. mysql.innodb_index_stats: n_diff_pfx01=2 (optimizer thinks only 2 distinct dates), sample_size=20, last_update at 03:14 (auto-recalc).
Root cause: monotonic increasing index + tiny random sample . New rows append at end; 20 sampled pages all from recent narrow date range → n_diff=2 → optimizer thinks selectivity terrible → full scan.
Fix:
SET SESSION innodb_stats_persistent_sample_pages=500; ANALYZE TABLE orders;→ n_diff_pfx01=412,883, plan back to range, 4.18 s → 0.31 s (13.5×).
Long-term: ALTER TABLE orders STATS_SAMPLE_PAGES=300 + global innodb_stats_persistent_sample_pages=300. Backup stats tables before ANALYZE (only way to undo).
5.2 Added Single-Column Index, Report SQL 200 ms → 2.1 s
Added idx_status (status) — 5 values, 'pending' ≈1%.
Plan: type=ref, key=idx_status, rows=421,800, Extra=Using filesort. EXPLAIN ANALYZE: Index lookup 1,988 ms (421k rows at 4.7 µs/row = random IO), sort 115 ms.
Why: idx_status only has (status, PK) → must lookup user_id, amount → 421k random PK lookups scattered across clustered index. Cost model: random lookup ×4 ≈ sequential 1.68 M pages; full scan only 150k pages → optimizer actually chose correctly for cost, but human misread rows drop as win.
Fix 1: Covering index (status, created_at, id, user_id, amount) → Covering index lookup, rows=100, 1.9 ms (105× faster than original).
Fix 2 (no DDL): Delayed join — subquery gets 100 PKs via index+sort, then join back → 14.6 ms.
Lesson: single-column low-cardinality index on OLAP-style query = negative optimization. Vet with EXPLAIN ANALYZE before creating.
5.3 Slow Log Shows 12 s UPDATE, Actual Execution 30 ms
Log: Query_time: 12.18, Lock_time: 11.90 (97.7%) → lock, not execution. sys.innodb_lock_waits: locked_table: order_item but waiting query is UPDATE orders → foreign key. fk_item_order ON DELETE CASCADE on order_item. Blocker: long transaction deleting/updating parent rows, holding 12,480 child locks for 4:38.
Fix: kill blocker (rollback takes minutes), then app-side batch delete (1,000 rows/commit). Drop FK CASCADE after app-level integrity checks (rebuild costs 10s of minutes on 110 M rows).
Result: Query_time max 12 s → 1.9 s, Lock_time share 97.7% → 22.3%.
Part 6: Two Hardened Scripts
6.1 slow-forensics.sh — read-only snapshot in one tarball:
processlist, innodb_trx, lock_waits, top digests, global status, ENGINE INNODB STATUS. Each query wrapped in timeout 20; credentials via --defaults-file (chmod 600); output dir/package chmod 700/600.
6.2 explain-compare.sh — structured before/after diff: EXPLAIN FORMAT=JSON + EXPLAIN ANALYZE + table stats. Diff mode uses jq to show rows_examined_per_scan (421,800 → 100) and cost (423k → 1,021). Note: 8.3+ uses JSON format version 2; ensure explain_json_format_version matches across comparisons.
Part 7: Production Change Risk Matrix & Acceptance Gates
Add secondary index (INPLACE): single table read/write slower; no DML block; rollback seconds (DROP)
Drop index: dependent SQL suddenly slow; none; hours (rebuild) ANALYZE TABLE: plan may shift globally; none; no direct undo
Shrink innodb_buffer_pool_size: instance-wide; minutes of collapse; grow back online
Drop FK: data integrity; none; full table check (hours)
Standard index add flow: pre-check disk (>2× index size), replica lag <1s, no long txns; backup SHOW CREATE TABLE + stats tables; execute on replica first; 30-min observation window; one-line rollback DDL.
Quantitative acceptance gates:
1. Target SQL P99 ≤ 50 ms
2. Select_scan not increased
3. Write latency not degraded (check file_summary_by_event_name for innodb_data_file) — most overlooked; wide index can slow writes 15% while reads improve 100×.
Part 8: Core Engineering Iron Laws
Query_time+ Lock_time together; if lock >30%, indexing is waste — it's a transaction design issue. EXPLAIN rows = estimate; EXPLAIN ANALYZE rows = truth. Order-of-magnitude gap = distrust all EXPLAIN -based conclusions. type=index ≠ "used index" — it's full index scan, same complexity as table scan on large tables.
Large ordered rows beat small unordered rows (5.2: 4,820 → 421,800 rows but 68× faster via early LIMIT termination).
Single-column low-cardinality index can be negative optimization (5.2: 200 ms → 2.1 s via massive random lookups).
Monotonic columns ( created_at, auto-inc) suffer stats distortion: 20-page sample may yield n_diff=2. Mitigate with STATS_SAMPLE_PAGES. log_slow_extra=ON mandatory — without Rows_examined / Rows_sent you only know "slow", not "why".
FK CASCADE = lock amplifier; parent update blocked by child locks; slow log shows parent SQL, misdirecting investigation. ANALYZE TABLE has no undo. Dump innodb_index_stats before running.
8.4 upgrade must-check: innodb_adaptive_hash_index=OFF, innodb_change_buffering=none, innodb_log_file_size replaced by innodb_redo_log_capacity (old config silently ignored).
Signed-in readers can open the original source through BestHub's protected redirect.
This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactand we will review it promptly.
How this landed with the community
Was this worth your time?
0 Comments
Thoughtful readers leave field notes, pushback, and hard-won operational detail here.
