Master MySQL Slow Query Analysis & Optimization: A Practical Guide

MySQL Slow Query Analysis and Optimization

Applicable Scenarios & Prerequisites

Applicable scenarios: SQL performance optimization, slow query identification, index tuning, database tuning.

Prerequisites:

MySQL 5.7+ / 8.0+

root or PROCESS privilege

Understanding of indexes and execution plans

Environment and Version Matrix

Component – Version – Notes

MySQL – 5.7 / 8.0 – Production recommended 8.0

pt-query-digest – 3.x – Percona Toolkit

mysqltuner – 1.9+ – Performance tuning tool

Quick Checklist

Enable slow query log

Configure slow query threshold

Analyze slow query log

Use EXPLAIN to analyze execution plan

Create/optimize indexes

Optimize SQL statements

Configure database parameters

Monitor slow query alerts

1. Enable Slow Query Log

View current configuration

SHOW VARIABLES LIKE 'slow%';
-- slow_query_log         | OFF
-- slow_query_log_file    | /var/lib/mysql/slow.log
-- long_query_time        | 10.000000

SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
-- log_queries_not_using_indexes | OFF

Dynamic enable (effective immediately, lost after restart)

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- record queries longer than 2 seconds
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- record queries not using indexes

Permanent configuration (modify my.cnf)

# /etc/my.cnf or /etc/mysql/my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 100  # record only when at least 100 rows are examined

# Restart MySQL
sudo systemctl restart mysqld

2. Slow Query Log Analysis

View slow query log

# Show last 20 lines
tail -n 20 /var/lib/mysql/slow.log

# Real‑time monitoring
tail -f /var/lib/mysql/slow.log

Log example:

# Time: 2025-10-24T10:15:30.123456Z
# User@Host: app[app] @ [192.168.1.100]
# Query_time: 5.123456  Lock_time: 0.000123  Rows_sent: 1000  Rows_examined: 500000
SET timestamp=1729764930;
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';

Key metrics: Query_time: execution time (seconds) Lock_time: lock wait time Rows_sent: rows returned Rows_examined: rows scanned (critical; large gap to Rows_sent indicates inefficiency)

Analyze with pt‑query‑digest

Installation:

# RHEL/CentOS
sudo yum install -y percona-toolkit

# Ubuntu
sudo apt install -y percona-toolkit

Generate report:

pt-query-digest /var/lib/mysql/slow.log > slow-report.txt
pt-query-digest /var/lib/mysql/slow.log --limit 10
pt-query-digest /var/lib/mysql/slow.log --since '2025-10-24 00:00:00' --until '2025-10-24 23:59:59'

Report highlights:

# Query 1: 0.50 QPS, 2.50s avg time, ID 0xABC123
# Count 100  total 1000
# Exec time 80  2500s  avg 2.5s
# Rows sent 50 50000
# Rows examined 90 500000
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'\G

Key metrics: QPS: queries per second Exec time: total execution time (percentage indicates share) Rows examined/Rows sent: ratio, ideally close to 1

3. EXPLAIN Execution Plan Analysis

Basic EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending'\G

Sample output:

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ALL               # full table scan (bad)
possible_keys: NULL
key: NULL                # no index used (bad)
rows: 500000             # estimated rows scanned
Extra: Using where

Key fields explanation

type : access type, order of efficiency const > eq_ref > ref > range > index > ALL

key : index actually used, should be non‑NULL

rows : estimated rows scanned, smaller is better

Extra : additional info, avoid “Using filesort”, “Using temporary”

type ranking (best to worst): const: primary key/unique index constant lookup (best) eq_ref: unique index join ref: non‑unique index lookup range: range scan (BETWEEN, IN, >) index: full index scan ALL: full table scan (worst)

EXPLAIN practical examples

Case 1: Full table scan optimization

EXPLAIN SELECT * FROM orders WHERE user_id = 12345\G
-- type: ALL
-- rows: 500000
-- key: NULL

Optimization: add index

CREATE INDEX idx_user_id ON orders(user_id);
EXPLAIN SELECT * FROM orders WHERE user_id = 12345\G
-- type: ref
-- rows: 100
-- key: idx_user_id  # index used

Case 2: Composite index optimization

SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY created_at DESC;
-- EXPLAIN result:
-- type: ref
-- key: idx_user_id
-- Extra: Using where; Using filesort  # filesort hurts performance

Optimization: create covering index

CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
EXPLAIN SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending' ORDER BY created_at DESC\G
-- type: ref
-- key: idx_user_status_created
-- Extra: Using index  # index covering (optimal)

4. Index Optimization Strategies

Composite index left‑most prefix rule

-- Index: idx_abc(a, b, c)

SELECT * FROM t WHERE a = 1;                     # uses a
SELECT * FROM t WHERE a = 1 AND b = 2;             # uses a, b
SELECT * FROM t WHERE a = 1 AND b = 2 AND c = 3;   # uses a, b, c

SELECT * FROM t WHERE b = 2;                         # cannot use index
SELECT * FROM t WHERE c = 3;                         # cannot use index

Index selectivity

Definition: selectivity = DISTINCT(column) / COUNT(*), closer to 1 is better.

Check selectivity:

SELECT
  COUNT(DISTINCT user_id) / COUNT(*) AS user_id_selectivity,
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity
FROM orders;
-- user_id_selectivity: 0.85  # good
-- status_selectivity: 0.02  # poor (few distinct values)

Recommendations:

High‑selectivity columns should be indexed first (e.g., user_id)

Low‑selectivity columns should not have separate indexes (e.g., status)

Composite indexes: place high‑selectivity columns first (user_id, status)

Index inspection and cleanup

Find unused indexes (MySQL 8.0+):

SELECT * FROM sys.schema_unused_indexes;

Check index size:

SELECT
    table_name,
    index_name,
    ROUND(stat_value * @innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE database_name = 'your_db'
ORDER BY stat_value DESC;

Delete redundant indexes:

SELECT * FROM sys.schema_redundant_indexes;
ALTER TABLE orders DROP INDEX idx_user_id_old;

5. SQL Optimization Tips

Avoid SELECT *

-- Bad
SELECT * FROM orders WHERE user_id = 12345;

-- Good
SELECT id, user_id, total_amount FROM orders WHERE user_id = 12345;

Pagination optimization

-- Bad (deep offset)
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- Good (use primary key range)
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

IN vs EXISTS

-- Small table drives large table: use IN
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE active = 1);

-- Large table drives small table: use EXISTS
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active = 1);

Avoid functions that break indexes

-- Bad (index disabled)
SELECT * FROM orders WHERE DATE(created_at) = '2025-10-24';

-- Good (use range)
SELECT * FROM orders
WHERE created_at >= '2025-10-24 00:00:00'
  AND created_at <  '2025-10-25 00:00:00';

6. Database Parameter Tuning

InnoDB buffer pool

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Recommended: 50‑80% of physical memory
SET GLOBAL innodb_buffer_pool_size = 8G;  # for a 16 GB server

Query cache (MySQL 5.7, removed in 8.0)

SHOW VARIABLES LIKE 'query_cache%';
-- MySQL 8.0 no longer supports query_cache

Connection limits

SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500;

7. Monitoring & Alerting

Prometheus + mysqld_exporter

Install exporter:

wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xf mysqld_exporter-*.tar.gz
cd mysqld_exporter-*/

# Create monitoring user
mysql -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'password';"
mysql -e "GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"

# Start exporter
export DATA_SOURCE_NAME='exporter:password@(localhost:3306)/'
./mysqld_exporter &

PromQL slow‑query alerts:

# Slow query rate
rate(mysql_global_status_slow_queries[5m]) > 10

# Slow query proportion
rate(mysql_global_status_slow_queries[5m]) / rate(mysql_global_status_questions[5m]) > 0.05

Real‑time slow query monitoring

SELECT
    id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE time > 2
ORDER BY time DESC;

-- Kill a slow query
KILL 12345;  -- where 12345 is the query id

8. Practical Case: Order Query Optimization

Initial SQL (5 s):

SELECT o.*, u.username
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

EXPLAIN result:

orders | ALL  | NULL | 500000 | Using where; Using filesort
users  | ref  | PRIMARY | 1   | NULL

Problems: full table scan on orders, filesort.

Optimization steps:

Create composite index on (status, created_at)

CREATE INDEX idx_status_created ON orders(status, created_at);

Re‑run EXPLAIN

orders | ref | idx_status_created | 1000 | Using index
users  | ref | PRIMARY            | 1    | NULL

Result: query time reduced from 5 s to ~50 ms.

Best Practices

Set slow‑query threshold to 1‑2 s in production.

Index fields used in WHERE, JOIN, ORDER BY; prioritize high‑selectivity columns.

Regularly run pt‑query‑digest (weekly) to analyze slow queries.

Run EXPLAIN on all SQL before deployment.

Alert when slow‑query rate exceeds 10 queries/s.

Periodically remove unused indexes.

Adjust innodb_buffer_pool_size to 70 % of memory.

Consider read‑write splitting for heavy read workloads.

Shard tables exceeding tens of millions of rows.

Cache hot data with Redis.