MySQL Slow Query Analysis & SQL Optimization: A Practical DBA Guide

Problem Background: Slow Queries Are Never Just "One Slow SQL"

The article opens with a real incident: at 2 AM, order database CPU hits 95%, API timeout rate 12%. SHOW PROCESSLIST shows many Sending data queries; innodb_trx has 3 transactions running >300s. Killing long transactions temporarily drops CPU, but the problem recurs next day. The real culprit, found in slow logs on day three, is a seemingly normal pagination query:

SELECT id, order_no, user_id, amount, status, created_at
FROM t_order
WHERE user_id = 10086 AND status = 1
ORDER BY created_at DESC
LIMIT 200000, 20;

This query runs 30K times/day, each taking 0.8–2s. Though individual latency is below the 2s slow-log threshold, it consumes 400s of DB time per minute — the true resource black hole. The long transactions were merely victims waiting for locks held by batch updates that lacked indexes and scanned full tables. The key insight: slow-query governance is about finding the real culprit, not just adding indexes when something looks slow. Total resource consumption = execution count × single latency; a 50ms query running 2000x/sec is more costly than a 5s query running 3x/day.

Applicable & Non-Applicable Scenarios

Applicable: MySQL 8.0/8.4 LTS/9.x (9.7 latest LTS); single, replication, MGR, or cloud RDS with slow log and performance_schema access; symptoms like sporadic timeouts, periodic CPU spikes, slow-log surges, replication lag, connection exhaustion; teams needing a discover→locate→optimize→verify→prevent-regression loop.

Not applicable: Application-layer issues (N+1, pool config, large transactions); non-InnoDB engines; sharding middleware routing; automated SQL rewrite/index platforms; MySQL 5.7 (EOL, lacks EXPLAIN ANALYZE, hash joins, histograms).

Version Baseline & Key Differences

A table summarizes MySQL versions as of 2026: 5.7 (EOL, no EXPLAIN ANALYZE, hash join, histograms); 8.0 (community EOL 2026, EXPLAIN ANALYZE from 8.0.18, hash join from 8.0.20, invisible indexes from 8.0.23); 8.4 LTS (5-year premier support, removes mysql_native_password default, dynamic innodb_redo_log_capacity); 9.x Innovation (short support); 9.7 LTS (Apr 2026, four-part versioning MAJOR.MINOR.PATCH.UPDATE, removes replica_parallel_type); post-9.7 uses calendar versioning YY.M.

Three critical facts: 9.7 is the last sequential version; mysql_native_password removed in 9.0 (old clients fail); 8.4 vs 9.x optimizer behavior differs (e.g., ALTER TABLE ... RENAME COLUMN lock escalation). Article uses 8.4 LTS as baseline, notes 9.x differences. All

EXPLAIN

rows

are estimates;

EXPLAIN ANALYZE

actual rows

are real — this distinction is the #1 source of wrong conclusions.

Environment Assumptions & Schema Baseline

MySQL 8.4.x LTS InnoDB, 1-primary-2-replica async replication, 16C/64GB/NVMe, utf8mb4/utf8mb4_0900_ai_ci, Prometheus+mysqld_exporter monitoring, single-table 5M–200M rows. Two tables provided: t_order (PK id, UK order_no, indexes idx_user_status_created(user_id,status,created_at), idx_created(created_at)) and t_order_item (PK id, index idx_order_id). The schema intentionally contains typical flaws: low-cardinality status, unindexed remark, redundant idx_created.

Pre-Flight Checks

Commands to verify version, engine, slow-log settings ( slow_query_log=ON, long_query_time=1, log_slow_extra=ON, min_examined_row_limit=100, log_throttle_queries_not_using_indexes=10 to avoid disk-full), performance_schema and key consumers ( events_statements_current, history, statements_digest), table sizes. Common pitfall: performance_schema ON but statements_digest consumer OFF → events_statements_summary_by_digest returns empty, falsely suggesting no slow SQL.

Core Knowledge: Where Time Goes

Four stages: ① network/transfer (large result sets), ② parse/optimize (rare complex SQL), ③ data access (scan rows ≫ returned rows), ④ lock waits (variable latency, concurrency-related). Primary investigation order: check locks first (via sys.innodb_lock_waits or performance_schema.data_lock_waits), then scans. If max_ms/avg_ms > 20 and SUM_LOCK_TIME ≈ SUM_TIMER_WAIT, lock is main cause.

Slow Log: What It Tells and What It Doesn't

Three hard limits: ① only logs statements exceeding long_query_time (default 10s, often lowered to 1–2s) — a 1.9s query running 500x/sec never appears; ② no aggregation — 100K executions = 100K log lines; ③ single-execution granularity — misses queue waits and slow client fetch. Still, slow log is the only source for full SQL text + plan + lock time + rows examined. Recommended config (8.0.14+): log_slow_extra=ON for extra fields. Dynamic changes via SET GLOBAL are lost on restart; must persist in my.cnf. Avoid log_output=TABLE (hotspot on mysql.slow_log).

performance_schema: Aggregation Workhorse

events_statements_summary_by_digest

normalizes literals to ?, aggregating same-shape queries. Key query orders by SUM_TIMER_WAIT (total latency), not average. Critical fields: COUNT_STAR (exec count), SUM_TIMER_WAIT (total ps), AVG_TIMER_WAIT, MAX_TIMER_WAIT, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, exam_per_sent ratio, SUM_NO_INDEX_USED, SUM_CREATED_TMP_DISK_TABLES, SUM_SORT_ROWS / SUM_SORT_SCAN. Sorting by SUM_TIMER_WAIT catches high-frequency moderate queries that dominate cumulative load.

Limitations: ① capacity capped by performance_schema_digests_size (default 10K); overflow goes to DIGEST IS NULL bucket. ② Memory-only — lost on restart; must snapshot periodically (example INSERT INTO monitor.slow_digest_snapshot ... every 5 min). ③ DIGEST_TEXT truncated at 1024 bytes; use STATEMENT_DIGEST_TEXT() or slow log for full text. Delta between two snapshots answers "who's eating DB right now".

sys Schema: Human-Readable Views

Views like sys.statement_analysis (ordered by total_latency), statements_with_full_table_scans, statements_with_temp_tables, statements_with_sorting, innodb_lock_waits. Example output shows rows_examined_per_call=1820 vs 20 rows returned — clear optimization target. Caveats: views compute real-time (overhead on busy systems), column names vary by minor version, default filter hides low-latency statements.

Per-Query Time Accounting with EXPLAIN ANALYZE

EXPLAIN ANALYZE

(8.0.18+) shows actual operator timings. Example: Limit actual time 0.089..842ms (first row vs all rows; large gap = blocking sort/agg); Index lookup returns 98,765 rows for 20 output; Sort node only 0.006ms — time spent reading/filtering 98K rows. The index idx_user_status_created(user_id,status,created_at) only uses user_id prefix; status and created_at not used for positioning, causing post-filter sort. Risk: EXPLAIN ANALYZE really executes — never on UPDATE / DELETE; on large SELECT run on read-only replica ( super_read_only=ON).

Reading EXPLAIN: Fields That Deceive

Four formats: traditional table, FORMAT=TREE (8.0.16+, closest to optimizer), FORMAT=JSON (full cost, used_columns), ANALYZE (real execution). Use in that order.

Traditional columns: id (query block order), select_type (SIMPLE/PRIMARY/SUBQUERY/DERIVED/UNION — DERIVED = materialized subquery, often a pit), table (or <derivedN>), partitions, type (access type — reliability medium), possible_keys, key (actual index), key_len (bytes used — high reliability, key to knowing how many composite index columns used), ref, rows (estimate — low reliability), filtered (estimate % — low), Extra (high). key_len calculation: BIGINT =8, TINYINT =1, DATETIME =5 (8.0+). VARCHAR(32) utf8mb4 = 32×4+2=130. NULL adds 1 byte. type=index means full index scan (O(n)), not "index used"; judge by rows ≈ table rows. type=ALL not always bad (small tables). type=range can still scan tens of millions. type=index_merge often slower than composite index.

Critical Extra keywords: Using where (server filter, check filtered), Using index (covering, good), Using index condition (ICP, good), Using filesort (heavy — align index order with ORDER BY), Using temporary (heavy — GROUP BY/DISTINCT/UNION), Using join buffer (Block Nested Loop) (heavy — driven table lacks index), Using join buffer (hash join) (8.0.20+), Using MRR (good), Using index for group-by (loose index scan, good). Using temporary + Using filesort together = almost certain problem; monitor Created_tmp_disk_tables.

Why Estimated Rows Go Wrong & How to Fix

Three causes: ① Stale stats after bulk load/delete — check

mysql.innodb_index_stats

last_update

; run ANALYZE TABLE (samples 20 pages by default; increase innodb_stats_persistent_sample_pages to 200 temporarily for large tables, off-peak only). ② Data skew — optimizer assumes uniform distribution; status=1 89% but optimizer thinks 25%. Histograms help ( ANALYZE TABLE ... UPDATE HISTOGRAM ON status WITH 16 BUCKETS) but only affect filter estimates, not index choice; must refresh after distribution changes. ③ Join estimation cascade — each table's rows × filtered feeds next; early error compounds. EXPLAIN FORMAT=JSON shows rows_examined_per_scan and filtered per table; compare with

EXPLAIN ANALYZE

actual rows

.

Nine Real Reasons Indexes Aren't Used

Left-prefix break: composite (a,b,c) needs continuous prefix; WHERE b=1 AND c=2 unusable; WHERE a=1 AND c=2 uses only a. ORDER BY also needs prefix continuity.

Implicit type conversion: VARCHAR column vs numeric literal → column cast to double per row, index lost. Reverse (numeric column vs string) is safe. Detect via SUM_NO_INDEX_USED + DIGEST_TEXT LIKE; EXPLAIN FORMAT=JSON + SHOW WARNINGS shows "Cannot use ref access... due to type conversion".

Function/expression on column: DATE(created_at) = '...' kills index; rewrite to range created_at >= '...' AND created_at < '...' (left-closed right-open). 8.0 functional indexes ( ADD INDEX idx_month ((DATE_FORMAT(created_at,'%Y-%m')))) work but require exact expression match and NOT NULL columns; prefer SQL rewrite.

LIKE leading wildcard: LIKE '%abc%' full scan; alternatives: prefix match, reversed column + prefix index, ngram fulltext, external search engine (ES).

OR conditions: if one side lacks index → full scan; rewrite to UNION ALL with deduplication logic.

Tiny table: optimizer correctly chooses full scan over index; don't force index.

Low selectivity: selectivity = distinct/total; <0.01 single-column index useless; 0.01–0.1 only with strong other predicates; >0.1 good. status (4 values, 89% one value) selectivity ~0.0000002 — near zero.

Index Skip Scan (8.0.13+): allows using composite index without leftmost column if left column has few distinct values (e.g., hundreds). Shows Using index for skip scan. Limited value — don't rely on it to fix bad design.

Optimizer picks wrong index: cost model error due to stale stats/skew. Fixes: better index (root), stats/histogram (symptom), FORCE INDEX (emergency only — fails if index dropped, prevents adaptive re-optimization). optimizer_trace reveals decision process (test only).

Index Design: Reverse-Engineer from Query Patterns

Three principles: ① Equality first, range second, sort last — [equality cols] + [range col] + [sort cols]. ② Covering index — append SELECT columns to avoid random I/O back to table. Example: idx_q1(user_id,status,created_at,order_no,amount) for query selecting those five columns. Trade-off: wider index = higher write cost, lower buffer pool efficiency; only for true hot queries. ③ Limit index count — ≤6 per table; each secondary index is a B+ tree maintained on writes. Find unused indexes via sys.schema_unused_indexes (needs full business cycle, not post-restart).

Real-world design exercise: three high-frequency queries (user order list 3M/day, merchant reconciliation 2/day, unpaid order scan 1/min) → derived indexes: idx_q1(user_id,status,created_at,id,order_no,amount) (equality+sort+cover), idx_merchant_created(merchant_id,created_at) (equality+range), idx_status_created(status,created_at) (equality+range for scan). Verify with EXPLAIN ANALYZE.

SQL Rewriting: Ten Techniques to Turn Slow into Fast

Deep pagination: LIMIT 200000,20 scans 200K rows. Fixes: cursor pagination ( WHERE (created_at,id) < (last_created,last_id) using row constructor, needs (user_id,created_at,id) index), delayed join (subquery fetches 20 IDs from index only, then joins), or business limit (max 50 pages).

Subquery to JOIN: correlated subquery executes per outer row; rewrite to LEFT JOIN ... GROUP BY leverages hash join (8.0.20+). Watch for NULL from SUM → COALESCE.

EXISTS vs IN: small subquery + large outer → IN; large subquery + small outer → EXISTS; 8.0 often rewrites both to hash semi-join — just EXPLAIN both.

UNION to UNION ALL: if sets disjoint, UNION ALL avoids temp table + sort ( Using temporary disappears).

Batch UPDATE via JOIN: single UPDATE ... JOIN tmp_table replaces N round-trips. Lock scope = batch size; chunk by PK range ( WHERE id BETWEEN ...) not LIMIT in subquery (error 1093).

COUNT optimization: COUNT(*) / COUNT(1) / COUNT(id) same in InnoDB. Large table: approximate via SHOW TABLE STATUS (50% error), counter table, or drop total-count pagination ("load more").

ORDER BY + LIMIT index: single-direction sort uses index; mixed DESC, ASC needs descending index (created_at DESC, id ASC) (8.0+). Verify: no Using filesort, type=index/range.

GROUP BY loose index scan:

SELECT user_id, MAX(created_at) FROM t_order GROUP BY user_id

shows Using index for group-by (fast). Adding SUM(amount) breaks it; need covering index (user_id,amount).

Window functions vs self-join:

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)

replaces self-join for "latest per group". Not always faster — requires full sort; only wins when groups few and index supports partition+order.

Avoid SELECT *: reads extra columns, prevents covering index, triggers off-page reads for TEXT/BLOB. Explicit column list often fixes "mysteriously slow" queries.

Schema-Level Optimization

Data types: status → TINYINT (not VARCHAR); money → DECIMAL(12,2) or BIGINT cents (not float); time → DATETIME / TIMESTAMP (not string); PK → auto-increment BIGINT or ordered UUID (UUIDv7) — random UUID kills write throughput (1/3 of auto-inc); IP → INT UNSIGNED + INET_ATON; boolean → TINYINT(1); enum → TINYINT + dict table.

Large column split: move rarely-used remark VARCHAR(255) to t_order_ext (1:1 PK). Benefit: shorter main rows → more rows/page → better buffer pool. Trigger when avg_row_length > 500 and rows > 10M.

Partitioning: useful for time-based archiving with range queries. Hard constraints: PK/UK must include partition column (forces composite PK (id,created_at)); partition pruning needs constant conditions ( DATE(created_at)=... fails); cross-partition queries slower; too many partitions (hourly = 8760) exhaust file handles. Prefer separate tables + app routing or periodic archival.

Online DDL discipline: prefer ALGORITHM=INSTANT (metadata-only), then ALGORITHM=INPLACE (rebuilds, no DML block but heavy IO, hours on 200M rows). Test INSTANT support via dummy column add/drop. Risks: long transactions block DDL (MDL), DDL queue causes connection avalanche. Mandatory: use pt-online-schema-change / gh-ost; check innodb_trx for long transactions; set SESSION lock_wait_timeout=5 (DDL gives up after 5s, avoids MDL avalanche); monitor replica lag; have rollback plan ( DROP INDEX also INPLACE but slow; type change = COPY, rollback = reverse DDL).

End-to-End Case Study

Symptom: 10-11 AM CPU 85%+, API P99 120ms→3.5s, timeout 8%. QPS +15%, CPU ×4 → per-query cost up. Slow log ×30. No replica lag.

Evidence: 5-min snapshot delta from events_statements_summary_by_digest shows top digest: order list 1.82M execs, 6842s total, avg 3.76ms, scanned 33M rows, returned 3.6M (scan/return=9.1). Second: COUNT 120K execs, all no_idx, scanned 14M rows.

Plan analysis: EXPLAIN ANALYZE on order list: Index lookup on idx_user_status_created with user_id=10086 only (key_len=8), returns 98,765 rows, filters to 1,180 ( status=1), sorts 98K rows → 1.8s. Lock check: zero waits.

Root cause: Index design assumed few orders per user; big customer grew to 98K orders. status=1 89% so filter barely reduces rows; optimizer chose "scan all user's orders then filter" over "use status in index". LIMIT 20 misled optimizer into thinking cost low.

Fixes: ① Covering index idx_q1(user_id,status,created_at,id,order_no,amount) → EXPLAIN ANALYZE shows Index range scan 20 rows, 0.09ms, no sort. ② Cursor pagination rewrite. ③ COUNT now uses new index.

Verification: 30-min post-fix: avg_us 3757→<100, scan_ratio 9.1→1.0; sys.schema_index_statistics shows new index reads; Innodb_rows_read/Questions drops; API P99 3.5s→150ms, timeout 0%. Rollback thresholds: write TPS drop >15% or disk growth >20% in 24h.

Lessons: slow-log threshold (2s) hid culprit → lower to 0.5s (below SLO 200ms) + minute-level digest ranking; index design must stress-test "head" users; COUNT queries unmonitored → all pagination totals need SQL review.

Locks & Concurrency: The Other "Slow" Path

Quick blocker identification: sys.innodb_lock_waits joined with innodb_trx shows waiting/blocking threads, locked table/index, block duration, blocking query. performance_schema.data_lock_waits + data_locks shows LOCK_MODE: X (exclusive), X,REC_NOT_GAP (record only), X,GAP (gap only), X (next-key = record+gap), S (shared), X,INSERT_INTENTION (insert waiting for gap). X,GAP + X,INSERT_INTENTION = classic gap-lock deadlock from range scan without proper index.

Gap locks (RR isolation): range UPDATE without index locks entire scanned range (next-key). Fixes: ① add index on WHERE (root), ② lower to READ COMMITTED (no gap locks except FK/unique checks; requires binlog_format=ROW, accepts non-repeatable reads), ③ shorten transactions.

MDL (metadata lock) avalanche: long SELECT holds MDL shared → DDL waits for exclusive → subsequent SELECTs queue behind DDL → connection pool exhaustion. Detect via sys.schema_table_lock_waits. Kill zombie transaction or DDL; KILL triggers long rollback. Prevent: run SELECTs autocommit/outside transaction; check innodb_trx before DDL; lock_wait_timeout=5 on DDL; no DDL at peak.

Deadlock analysis: SHOW ENGINE INNODB STATUS → LATEST DETECTED DEADLOCK shows each transaction's held locks (index) and waited locks. Cross-lock order (T1: uk_order_no → PK; T2: PK → uk_order_no) = fix by uniform lock order (e.g., sort by PK before update). Enable innodb_print_all_deadlocks=ON for error-log history.

Connection saturation: Threads_connected / Threads_running; Sleep hundreds = app pool misconfig; Waiting for table metadata lock = MDL; Sending data = slow queries. Fix root causes first, don't mass-kill Sleep (causes reconnect storm).

Parameters: Common Mis-tunes

Per-connection buffers (dangerous to increase): sort_buffer_size (256KB default, per connection — 64MB × 500 = 32GB OOM), tmp_table_size / max_heap_table_size (16MB, min of two), join_buffer_size (256KB per join per connection), read_buffer_size (128KB). Fix sorts by adding indexes, not buffers. Sort_merge_passes growth → sort buffer too small, but check Sort_scan / Sort_range first.

Buffer pool: hit rate = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests; target >99%. <95% → either pool too small or one full scan polluted cache — optimize that SQL first. Online resize (chunk-based) safe to grow, risky to shrink (flush storm).

Slow-query switches: long_query_time=0.5, log_slow_extra=ON, log_queries_not_using_indexes=OFF (with throttle). optimizer_switch changes only after test + documentation; never copy-paste. innodb_parallel_read_threads=4 helps only full scans ( COUNT(*)), not point queries.

Never touch: innodb_flush_log_at_trx_commit=0/2 (data loss), sync_binlog=0 (replica drift), query_cache_size (removed 8.0), innodb_file_per_table=OFF (shared tablespace bloat), max_connections huge (per-connection memory, context-switch overhead — use proxy pool).

Pre-Deployment SQL Review Checklist

15 automated checks: WHERE exists? indexed? function on column? implicit cast? leading wildcard? SELECT *? deep pagination ( OFFSET>10000)? >3-table join? correlated subquery in SELECT? long transaction risk? scan/return >100x? temp table spill? batch size ≤1000? FOR UPDATE scope? new index redundant with existing prefix? Bash script example runs EXPLAIN on each SQL, flags type=ALL/index with rows>1000, Using filesort, Using temporary. Limitations: column positions vary by version; only handles SELECT; simplistic logic; credentials from ~/.my.cnf.

First hour post-deploy: new digests since deploy ( FIRST_SEEN), connection state anomalies, replica lag (>30s sustained = consider rollback), Created_tmp_disk_tables spike.

Daily Operations & Baseline

Daily: top 5 by SUM_TIMER_WAIT, Created_tmp_disk_tables growth. Weekly: top 20 + optimization plans, sys.schema_unused_indexes. Monthly: redundant index audit (prefix overlap), capacity trend. Quarterly: version upgrade assessment + plan regression.

Baseline table: daily snapshot of events_statements_summary_by_digest into monitor.digest_baseline. Post-release comparison: join baseline vs current, flag digests where avg_us doubled and >50ms. Answers "did this release slow any query 2x?" better than CPU charts.

Index redundancy: list all indexes with column sequences; if index A columns are prefix of B and A usage ≪ B, A may be redundant — but verify not a narrower covering index. Use INVISIBLE (8.0+) for one-week canary before DROP; INVISIBLE keeps write overhead, only hides from optimizer.

Scenarios Not Worth Optimizing

Small table full scan (<1000 rows, 0.1ms) — index may be slower.

Report queries scanning months of data (30s expected) — move to replica/OLAP (ClickHouse), pre-aggregate, or columnar engine.

Offline batch jobs (backup, reconciliation) — goal is non-interference, not speed; throttle concurrency, low innodb_lock_wait_timeout, run on replica.

One-off DDL/data fix — focus on lock/rollback safety, not duration.

Summary: Four Core Sentences

Sort by total latency ( SUM_TIMER_WAIT), not single latency. A 20ms×200/sec query consumes 345,600s/day vs a weekly 30s report.

EXPLAIN

rows

is estimate;

EXPLAIN ANALYZE

actual rows

is truth. Judge by scan/return ratio, not type=ALL (small table full scan is correct).

Index column order: equality → range → sort → cover . 90% of "index added but no effect" are order mistakes or broken by implicit cast/function/leading wildcard.

Rewrite SQL first (cursor pagination, JOIN over subquery), then index, then schema. ALTER TABLE last — requires off-peak + lock_wait_timeout + shadow-tool + rollback plan.

Three must-have mechanisms: slow-log threshold < SLO (200ms SLO → 0.5s threshold); post-release baseline diff (any query 2x slower?); weekly top-20 digest board — make "slow" a continuously visible number, not an alert-driven fire drill.

Final warning: no universal magic parameters ( sort_buffer_size=1G, innodb_flush_log_at_trx_commit=2, custom optimizer_switch). Every change must answer: which knob, why, how verified.