How to Diagnose MySQL Slow Queries: From Log Capture to Index Optimization

Problem Background

MySQL slow queries are a frequent cause of degraded response times during traffic spikes, often resulting from missing indexes, poor schema design, inefficient SQL, stale statistics, or insufficient hardware resources.

Applicable Scenarios

API response suddenly slows down

DB CPU stays high while QPS looks normal

Slow‑query log grows quickly

Legacy SQL needs optimization

Pre‑deployment SQL audit discovers performance risks

Replication lag caused by blocking queries

Planning sharding for large tables

Common Root Causes

1. Index layer : missing index on WHERE columns, index‑ineffective functions, wrong index choice, wrong column order in composite indexes.

2. SQL writing layer : SELECT * without covering index, unbounded JOIN, deep sub‑queries, OR conditions, large OFFSET, missing LIMIT.

3. Table structure layer : huge tables without partitioning, inappropriate column types, no partition, redundant columns.

4. DB configuration layer : too small buffer pool, bad dirty‑page policy, excessive connections, insufficient temp buffers.

5. Hardware & system layer : insufficient I/O, memory pressure, CPU limits, network latency.

Step 1 – Enable Slow‑Query Log

-- View current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';

-- Temporary enable (recommended long_query_time = 1s)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/mysql-slow.log';

-- Permanent enable (edit my.cnf)
# [mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE

-- Restart MySQL after editing

⚠️ Risk : Restart will drop all connections; ensure reconnection logic, no long‑running transactions, and backup config before applying.

1.4 Analyze with pt‑query‑digest

# Install Percona Toolkit if missing
sudo yum install percona-toolkit -y   # CentOS/RHEL
sudo apt-get install percona-toolkit -y   # Debian/Ubuntu

# Summarize slow log
pt-query-digest /var/lib/mysql/mysql-slow.log

# Show top 10 slow queries
pt-query-digest --limit 10 /var/lib/mysql/mysql-slow.log

# Filter by time range
pt-query-digest --since '2026-04-29 10:00:00' /var/lib/mysql/mysql-slow.log

# Exclude replication user
pt-query-digest --filter '$event->{user} =~ /^(?!repl_user)/' /var/lib/mysql/mysql-slow.log

The first entry in the output is the query that needs immediate attention.

Step 2 – Analyze Execution Plan with EXPLAIN

Run EXPLAIN (or EXPLAIN ANALYZE in MySQL 8.0) for the identified slow query.

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10;

type field (most important) describes how rows are accessed, ordered from best to worst:

system : single‑row system table – optimal.

const : unique index lookup – optimal.

eq_ref : primary/unique key join – normal.

ref : non‑unique index equality – acceptable.

range : index range scan – normal.

index : full index scan – usually needs improvement.

ALL : full table scan – severe, must be optimized.

rows shows the optimizer’s estimated row count; larger numbers usually indicate a problem.

Extra may contain: Using filesort – ORDER BY not using an index. Using temporary – GROUP BY / DISTINCT creates a temp table. Using index – covering index, good. Using where – filter applied after index lookup.

Common problematic combos: Using filesort + Using temporary – both sorting and temp table, worst case.

2.3 Real‑world Example

-- Original slow query
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10;
-- Output shows type=ALL, possible_keys=NULL, rows=1523872, Extra='Using where; Using filesort'

-- After adding index on (user_id, created_at)
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 ORDER BY created_at DESC LIMIT 10;
-- Output shows type=ref, possible_keys=idx_user_id_created, rows=10, Extra='Backward index scan; Using where'

2.4 Composite Index Order

-- Verify index order matches query predicates
EXPLAIN SELECT * FROM orders WHERE a = 1 AND b = 2 AND c > 3 ORDER BY d;
-- If Extra still shows Using filesort, create index (a,b,c,d)

Step 3 – Identify Common Index‑Failure Scenarios

3.1 Functions on Indexed Columns

-- Bad: WHERE YEAR(created_at) = 2026   → type=ALL
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Good: range query on raw column
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

3.2 LIKE Prefix Wildcard

-- Bad: LIKE '%wang%'
EXPLAIN SELECT * FROM users WHERE name LIKE '%wang%';
-- type=ALL

-- Good: LIKE 'wang%'
EXPLAIN SELECT * FROM users WHERE name LIKE 'wang%';
-- type=range (uses index)

-- If full‑wildcard needed, consider FULLTEXT or external search engine.

3.3 Implicit Type Conversion

-- Bad: numeric literal compared to VARCHAR column
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- Leads to full scan

-- Good: use string literal
EXPLAIN SELECT * FROM users WHERE phone = '13800138000';

3.4 OR Conditions

-- Bad: name='zhangsan' OR email='[email protected]' (email lacks index) → ALL
EXPLAIN SELECT * FROM users WHERE name='zhangsan' OR email='[email protected]';

-- Good: split into UNION so each side can use its own index
EXPLAIN SELECT * FROM users WHERE name='zhangsan'
UNION
SELECT * FROM users WHERE email='[email protected]';

3.5 Verify Index Usage

EXPLAIN SELECT * FROM orders WHERE status='completed' AND created_at > '2026-04-01';
-- Check possible_keys and key columns; if key is NULL but possible_keys not empty, optimizer chose full scan.
SHOW INDEX FROM orders;

Step 4 – Deep Pagination Issues

Large OFFSET forces MySQL to scan and discard rows.

SELECT * FROM orders WHERE status='completed' ORDER BY id DESC LIMIT 100000,10;

4.1 Cursor‑Based Pagination

-- First page
SELECT * FROM orders WHERE status='completed' ORDER BY id DESC LIMIT 10;
-- Remember last id (e.g., 987654)

-- Next page
SELECT * FROM orders WHERE status='completed' AND id < 987654 ORDER BY id DESC LIMIT 10;

Uses primary‑key index, O(log N + 10) instead of O(N).

4.2 Delayed Join

-- Original slow pagination
SELECT * FROM orders WHERE status='completed' ORDER BY id DESC LIMIT 100000,10;

-- Optimized: first fetch primary keys, then join back
SELECT o.* FROM orders o INNER JOIN (
  SELECT id FROM orders WHERE status='completed' ORDER BY id DESC LIMIT 100000,10
) t ON o.id = t.id;

4.3 Limit Total Pages

SELECT * FROM orders WHERE status='completed' AND created_at > '2026-04-01' ORDER BY id DESC LIMIT 10 OFFSET 100000;

Enforce a maximum OFFSET (e.g., 10 000) and prompt users to narrow search.

Step 5 – Multi‑Table JOIN Optimization

5.1 View JOIN Execution Plan

EXPLAIN SELECT u.name, o.order_no, o.amount FROM users u INNER JOIN orders o ON u.id=o.user_id WHERE u.status='active' AND o.created_at>'2026-04-01';

Key points: driver table, presence of ALL, correct index on join columns.

5.2 JOIN Principles

Small table drives large table : force with STRAIGHT_JOIN if needed.

Joined columns must be indexed : create INDEX idx_user_id (user_id) on orders if missing.

5.3 Common JOIN Problems

-- Problem: ORDER BY on column without index → Using filesort
EXPLAIN SELECT u.name, o.order_no FROM users u INNER JOIN orders o ON u.id=o.user_id WHERE u.status='active' ORDER BY o.created_at DESC;
-- Fix: add composite index (user_id, created_at) on orders
ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);

-- Problem: Subquery with IN (SELECT ...) in MySQL 5.7 → poor performance
EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status='inactive');
-- Fix: rewrite as JOIN
EXPLAIN SELECT o.* FROM orders o INNER JOIN users u ON o.user_id=u.id WHERE u.status='inactive';

-- Problem: LEFT JOIN condition placed in WHERE turns it into INNER JOIN
SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id=o.user_id WHERE o.status='completed';
-- Correct placement:
SELECT u.name, o.order_no FROM users u LEFT JOIN orders o ON u.id=o.user_id AND o.status='completed' WHERE o.id IS NULL;

Step 6 – Monitor Real‑Time Status and Processes

-- Show connection counts
SHOW STATUS LIKE 'Threads%';
-- Show max connections
SHOW VARIABLES LIKE 'max_connections';
-- List all threads
SHOW PROCESSLIST;
-- Show lock waits (MySQL 8.0)
SELECT * FROM performance_schema.data_lock_waits;
SELECT * FROM information_schema.INNODB_TRX;
-- Identify long‑running transactions (>60 s)
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
       trx_rows_locked, trx_query,
       ps.user AS mysql_user, host_info
FROM information_schema.INNODB_TRX
JOIN performance_schema.threads ps ON ps.processlist_id = trx_mysql_thread_id
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY duration_sec DESC;

⚠️ Risk : Killing a running transaction rolls back and may take longer; verify it’s not a normal long‑running job before issuing KILL.

Step 7 – Index Design and Best Practices

7.1 When to Add an Index

Columns appearing in WHERE, JOIN ON, ORDER BY, or GROUP BY are candidates. Prioritize high‑frequency queries.

-- Example high‑frequency filter
WHERE status='completed' AND created_at > '2026-04-01'
-- Recommended composite index (status, created_at)

7.2 Left‑most Prefix Rule

Index (a,b,c) can satisfy a=1, a=1&b=2, a=1&b=2&c=3, a IN(...), but not b=2 alone.

7.3 Covering Indexes

-- Create covering index for columns needed by query
ALTER TABLE orders ADD INDEX idx_cover (user_id, order_no, id);

EXPLAIN SELECT user_id, order_no, id FROM orders WHERE user_id=123;
-- Extra shows "Using index" → no table lookup needed

7.4 Design Guidelines

Avoid indexing low‑selectivity columns (e.g., status with only a few distinct values) unless they are the leftmost column of a composite index.

Use prefix indexes for long VARCHAR/TEXT columns: INDEX idx_email_prefix (email(10)). Choose the smallest prefix that still gives high selectivity.

Do not index columns that are updated very frequently (e.g., updated_at) because index maintenance adds overhead.

Step 8 – Configuration‑Level Optimizations

8.1 Buffer Pool

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Set to 50‑80% of physical RAM (e.g., 8G)
SET GLOBAL innodb_buffer_pool_size = 8589934592;   -- 8 GB (MySQL 8.0 allows online change)

Monitor Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads; aim for >95 % hit rate.

8.2 Slow‑Query Log Parameters

# In my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1   # MySQL 5.7 only
log_output = FILE
max_slow_log_size = 100M

8.3 Connection & Temporary Table Settings

SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 300;   # Adjust based on peak load

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- If many disk temp tables, increase:
SET GLOBAL tmp_table_size = 256M;
SET GLOBAL max_heap_table_size = 256M;

Step 9 – Full Troubleshooting Workflow Summary

9.1 Standard Process

Confirm slow‑query log is enabled; adjust long_query_time if needed.

Run pt‑query‑digest to list the slowest queries.

For each top query, execute EXPLAIN (or EXPLAIN ANALYZE).

Inspect type , rows , and Extra columns.

Map the observed issue to a concrete root cause (missing index, index misuse, deep pagination, JOIN driving, configuration).

Apply the appropriate fix (add/adjust index, rewrite SQL, use cursor pagination, tweak server parameters).

Re‑run EXPLAIN and benchmark (profiling or pt‑query‑digest) to verify improvement.

9.2 Key Judgement Criteria

type = ALL → full table scan, highest priority.

rows estimate > 100 k → likely inefficient.

Extra contains Using filesort or Using temporary → sorting or temp‑table overhead.

9.3 Most Common Slow‑Query Causes

Missing index on WHERE columns.

Functions or arithmetic on indexed columns.

Deep pagination with large OFFSET.

SELECT * preventing covering index usage.

JOIN where the driven table lacks an index.

9.4 Optimization Priority

Eliminate full table scans (add proper indexes).

Remove Using filesort by indexing ORDER BY columns.

Replace deep OFFSET with cursor‑based pagination.

Adjust server‑level settings (buffer pool, temp tables) if query‑level fixes are insufficient.

9.5 Daily Prevention Practices

Run EXPLAIN on every new SQL before deployment.

Schedule regular pt‑query‑digest analysis of the slow‑query log.

Ensure high‑concurrency queries have suitable indexes.

Avoid creating single‑column indexes on low‑selectivity fields unless part of a composite index.

Batch large DML operations to keep transaction duration short.