MySQL Slow Query Mastery: From Log Analysis to Index Optimization

Problem Background

"Database is slow" is the most common feedback for DBAs, but it carries almost zero information: are all queries slow or just a few? When did it start? Were there changes? Without slow query logs, execution plan analysis habits, and key metric monitoring, troubleshooting becomes guesswork, leading to unfounded optimizations that may introduce new issues.

Slow query troubleshooting is not mysticism but a repeatable method: first use slow query logs to find problematic SQL, then use EXPLAIN to examine execution plans, combine with index design principles to pinpoint the issue, and finally verify optimization results. This article structures the full troubleshooting loop and provides commands and configuration examples safe for cautious production use.

Note: MySQL 5.7 and 8.0 differ in some system table structures and features; version-specific differences are explicitly noted. Uncertain fields should be verified against the official documentation for the actual online version.

Applicable Scenarios

Business reports interface latency, suspected or confirmed database-layer issue.

Routine performance evaluation of new feature SQL before release.

Database CPU, IO, or connection count anomalies needing root cause identification.

Establishing normalized slow query monitoring and governance instead of reactive firefighting.

Performance degradation after data growth requires index redesign.

Core Knowledge Points

Storage Engine Prerequisites

The methods focus on InnoDB (default for most production MySQL). Details like row locking and online DDL are InnoDB-specific. If MyISAM tables exist, locking (table-level vs row-level) and DDL behavior differ; confirm target table engine first:

SELECT table_name, engine FROM information_schema.tables WHERE table_schema = 'shop';

Slow Query Log Mechanism

MySQL's slow query log records SQL statements exceeding long_query_time threshold. Default is usually off; must be enabled manually. Key points: long_query_time unit is seconds, supports decimals (e.g., 0.5 = 500ms).

Queries not using indexes are not logged by default; controlled separately by log_queries_not_using_indexes.

Logging overhead exists; setting threshold too low (e.g., 0 seconds) generates massive logs under high concurrency, impacting disk and IO.

EXPLAIN Execution Plan Core Fields

EXPLAIN

is the most important tool for root cause analysis. Focus on:

type : Access type, from best to worst:

system > const > eq_ref > ref > range > index > ALL

. ALL (full table scan) is a primary suspect, but small table scans can sometimes be faster than index access.

key : Actual index used; NULL means no index used.

rows : Estimated rows to scan; an estimate but sufficient for magnitude judgment.

Extra : Additional info. Using filesort (extra sort), Using temporary (temp table) are performance signals; Using index (covering index, no table lookup) is a good signal.

Common Causes of Index Failure

Using functions or arithmetic on indexed columns, e.g., WHERE YEAR(create_time) = 2026 kills index;

WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01'

works.

Implicit type conversion due to string/number mismatch, e.g., VARCHAR column queried with numeric literal.

Leading wildcard LIKE '%keyword' prevents index use; trailing wildcard LIKE 'keyword%' usually works.

Violating leftmost prefix of composite index: skipping first column and using later columns.

Using OR connecting conditions without separate indexes, or optimizer deems index cost higher than full scan.

Composite Index Leftmost Prefix Principle

For composite index (a, b, c), queries on a, a AND b, or a AND b AND c can use it. Queries only on b or c typically cannot (MySQL 8.0 may have skip scan in some cases; verify with actual EXPLAIN).

Typical Slow Query Patterns

Missing Index : EXPLAIN type is ALL.

Index Failure : Index exists but not used due to query writing.

Data Volume Surge : Previously reasonable index selectivity drops as data grows.

Lock Wait : SQL executes fast but waits for locks (row, table, metadata); slow log includes wait time, easily misjudged as slow SQL.

Resource Contention : SQL fine but overall DB load high (CPU, IO, connections saturated).

Overall Troubleshooting Approach

Symptom

Business reports interface slowdown, or monitoring alerts show rising DB response time, CPU, or IO anomalies.

Preliminary Judgment

Determine if slowdown is global (all queries) or local (specific interfaces/queries). Global → check resources (CPU, IO, connections, locks). Local → check specific SQL execution plans.

Command Check, Key Metrics, Root Cause, Fix, Verification, Rollback, Retrospective

Detailed in "Practical Steps" and "Troubleshooting Paths". Sequence: use slow query log and SHOW PROCESSLIST to lock down problem SQL, analyze with EXPLAIN, evaluate index optimization, validate in test, cautiously implement in production with continuous observation, and prepare rollback plan.

Practical Steps

Step 1: Confirm Slow Query Log Enabled; Enable If Not

Check current settings:

SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'slow_query_log_file'; SHOW VARIABLES LIKE 'long_query_time'; SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

Example output shows slow_query_log = OFF, long_query_time = 10. Enable dynamically (no restart):

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = 'ON';

Risk : SET GLOBAL affects only new sessions; existing connections unchanged. Settings lost on restart; persist in config file:

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

Judgment : 1 second is common starting point; for stricter latency (e.g., 200ms), temporarily lower to 0.2s for targeted analysis, but evaluate long-term log volume impact.

Step 2: Analyze Slow Query Log, Locate High-Frequency or High-Latency SQL

Raw log is text; use mysqldumpslow or pt-query-digest (Percona Toolkit) for aggregation.

mysqldumpslow -s at -t 10 /var/lib/mysql/slow.log

Example output:

Count: 245 Time=2.34s (573s) Lock=0.00s (0s) Rows=1.0 (245), root[root]@[192.168.1.50] SELECT * FROM orders WHERE customer_id = N AND status = 'S'

Judgment : Focus on two types: single-query extremely slow (complex query or missing index), and high-frequency even if single latency low but cumulative impact large (high-frequency interface inefficiency amplified by traffic). Count shows frequency, Time shows cumulative latency; combine for priority.

If pt-query-digest installed, richer dimensions:

pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_report.txt

Output includes SQL fingerprint summary sorted by latency proportion, execution count distribution, latency distribution.

Step 3: Check for Currently Executing Slow Queries or Lock Waits (Real-time)

If issue happening now, don't wait for log flush; check current sessions: SHOW PROCESSLIST; Example output shows sessions with Time=45, State=Waiting for table metadata lock (DDL holding lock) and State=Sending data (large scan).

Judgment : Focus on Time (seconds executing) and State. Many sessions with Waiting for table metadata lock or Waiting for lock indicate lock contention root cause (long transaction or DDL holding lock), not SQL efficiency. Sending data with high Time suggests heavy data scan/computation.

For lock waits, MySQL 5.7+ can query performance_schema lock tables (names/fields vary by version; verify with docs):

SELECT * FROM performance_schema.data_lock_waits; SELECT * FROM performance_schema.data_locks;

Judgment : Lock wait relationship tables reveal blocking chains, identifying the transaction holding locks (specific app connection or uncommitted manual transaction) — key to resolving lock waits.

Step 4: EXPLAIN Analysis on Identified Problem SQL

Example slow SQL:

SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

EXPLAIN SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

Problematic output: type=ALL, key=NULL, rows=890234 → full table scan, typical missing index.

Check existing indexes: SHOW INDEX FROM orders; If customer_id and status lack indexes, or only single-column indexes exist but query uses both, evaluate adding composite index.

MySQL 8.0 offers EXPLAIN ANALYZE for actual execution details (not just estimates), but it truly executes the SQL; caution in production for write operations or huge tables:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

Step 5: Design and Validate Index Optimization

Based on analysis, evaluate adding composite index:

-- In test environment first, not directly in production ALTER TABLE orders ADD INDEX idx_customer_status (customer_id, status);

Judgment : Composite index column order should match query patterns; put high-selectivity equality columns first. customer_id has much higher cardinality than status (few distinct values), so place first.

Re-run EXPLAIN to verify:

EXPLAIN SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped';

Optimized output: type=ref, key=idx_customer_status, rows=3 → from ALL to ref, rows from 890k to 3. Significant improvement, but final confirmation requires actual execution time and production observation, not just plan.

Step 6: Verify Actual Execution Time Improvement in Test

Enable profiling (MySQL 5.7+, still available in 8.0 but performance_schema recommended):

SET profiling = 1; SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped'; SHOW PROFILES;

Or simpler client-side timing:

SELECT NOW(3); SELECT * FROM orders WHERE customer_id = 10086 AND status = 'shipped'; SELECT NOW(3);

Judgment : Test environment data volume and index statistics (update via ANALYZE TABLE) must resemble production; otherwise measured speedup may not reflect real effect. Ideally test data scale matches production or at least distribution characteristics.

Step 7: Evaluate Production Index Creation Method

Production index creation is high-risk; assess:

Table size and estimated creation time : Larger tables take longer, may cause locking (depends on version/engine online DDL support). Check size:

SELECT table_name, table_rows, ROUND(data_length/1024/1024, 2) AS data_mb, ROUND(index_length/1024/1024, 2) AS index_mb FROM information_schema.tables WHERE table_schema = 'shop' AND table_name = 'orders';