Why Is Your SQL Slow? Deep Dive into Common MySQL Slow Query Patterns and Optimizations

1. Slow Query Log Configuration

1.1 Definition

Slow query log records SQL statements whose execution time exceeds the configured threshold ( long_query_time) and is used for performance tuning.

1.2 Configuration

-- View current settings
SHOW VARIABLES LIKE '%slow%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable temporarily (lost after restart)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  -- seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Permanent configuration (edit my.cnf)
# [mysqld]
# slow_query_log = 1
# slow_query_log_file = /var/log/mysql/slow.log
# long_query_time = 2
# log_queries_not_using_indexes = 1  -- record queries that do not use indexes

1.3 Relevant Parameters

long_query_time : threshold, default 10 s

min_examined_row_limit : minimum rows examined before logging

log_slow_admin_statements : record administrative statements

log_queries_not_using_indexes : record queries that do not use indexes

2. Using pt-query-digest

2.1 Installation and Basic Usage

# Ubuntu/Debian
sudo apt-get install percona-toolkit
# CentOS/RHEL
sudo yum install percona-toolkit

# Basic analysis
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# Analyze last 24 h
pt-query-digest --since=24h /var/log/mysql/slow.log

# Analyze a specific time range
pt-query-digest --since='2024-01-01 00:00:00' --until='2024-01-02 00:00:00' slow.log

2.2 Interpreting the Output

Overall statistics : total queries, unique queries, time range

Response‑time distribution

Query‑by‑query analysis : execution count, average/max time, total time share, rows examined/returned

Table‑scan statistics

2.3 Advanced Features

# Order by a specific field
pt-query-digest --order-by Query_time:sum slow.log

# Filter by database
pt-query-digest --filter '$event->{db} =~ m/^your_db/' slow.log

# JSON output
pt-query-digest --output json slow.log > report.json

# Real‑time monitoring
pt-query-digest --processlist h=localhost,u=root,p=password --interval 0.01

3. Common Slow‑Query Patterns

3.1 Full Table Scan

-- Example without index
SELECT * FROM users WHERE phone = '13800138000';
-- If the phone column lacks an index, a full table scan occurs

Identification features: rows_examined close to total rows

EXPLAIN shows

type=ALL

Key

column is NULL

3.2 Large Sort

-- Sorting a large data set
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 20;

Identification features:

Extra column shows Using filesort Temporary table on disk ( Using temporary; Using filesort)

Insufficient

sort_buffer_size

3.3 N+1 Query Problem

-- Query inside a loop
SELECT * FROM users WHERE id = ?;

Identification features:

Many similar queries differing only by parameter

Simple query but high execution count

Caused by application logic

3.4 Implicit Type Conversion

-- Comparing string with numeric column
SELECT * FROM users WHERE id = '123abc';
-- If <code>id</code> is numeric, this forces a full table scan

Identification features:

Indexed column not used

Data type mismatch

EXPLAIN shows

type=ALL

3.5 Poor JOIN

-- Cartesian product
SELECT * FROM users, orders;
-- Or missing JOIN condition

Identification features:

Result set unusually large

Execution time grows exponentially with data size rows_examined huge

4. SQL Rewrite Optimization Techniques

4.1 Index Optimization

-- Original query (age indexed, name not)
SELECT * FROM users WHERE age > 20 AND name LIKE '张%';

-- Option 1: add composite index
ALTER TABLE users ADD INDEX idx_age_name (age, name);

-- Option 2: rewrite order to use leftmost prefix (MySQL may optimise automatically, but explicit rewrite is safer)
SELECT * FROM users WHERE age > 20 AND name LIKE '张%';

4.2 LIMIT Pagination Optimization

-- Inefficient pagination (large OFFSET)
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;

-- Option 1: covering index + delayed join
SELECT * FROM orders
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 20
) AS tmp USING (id);

-- Option 2: cursor‑based pagination (when ordering column is unique)
SELECT * FROM orders WHERE id > last_seen_id ORDER BY id LIMIT 20;

4.3 JOIN Optimization

-- Original query that may create a huge temporary table
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id;

-- Option 1: select only needed columns
SELECT u.id, u.name, o.order_no, oi.product_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN order_items oi ON o.id = oi.order_id;

-- Option 2: split into steps
-- Step 1: fetch user order IDs
-- Step 2: batch fetch order details

4.4 Subquery Optimization

-- Inefficient IN subquery
SELECT * FROM users WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);

-- Option 1: replace with JOIN
SELECT DISTINCT u.* FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

-- Option 2: use EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.amount > 1000
);

4.5 GROUP BY Optimization

-- Inefficient GROUP BY that may trigger filesort
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id ORDER BY NULL;

-- Ensure index usage
ALTER TABLE orders ADD INDEX idx_user_id (user_id);

-- Covering index example
SELECT user_id, COUNT(*) FROM orders
WHERE status = 'completed'
GROUP BY user_id;
-- Add composite index (status, user_id)

4.6 UNION Optimization

-- Original UNION (deduplication and sorting)
SELECT id FROM users WHERE age < 18
UNION
SELECT id FROM users WHERE age > 60;

-- Option 1: UNION ALL when duplicates are impossible
SELECT id FROM users WHERE age < 18
UNION ALL
SELECT id FROM users WHERE age > 60;

-- Option 2: combine with OR
SELECT id FROM users WHERE age < 18 OR age > 60;

4.7 Function Index Optimization

-- Query that applies a function on a column, disabling index
SELECT * FROM users WHERE DATE(create_time) = '2024-01-01';

-- Option 1: avoid function on indexed column
SELECT * FROM users
WHERE create_time >= '2024-01-01 00:00:00'
  AND create_time < '2024-01-02 00:00:00';

-- Option 2: virtual column + index (MySQL 5.7+)
ALTER TABLE users
ADD COLUMN create_date DATE AS (DATE(create_time)) VIRTUAL,
ADD INDEX idx_create_date (create_date);

5. Continuous Slow‑Query Optimization Process

Monitoring first : continuously collect slow‑query logs.

Analysis and定位 : use tools such as pt-query-digest to pinpoint root causes.

Optimization implementation : rewrite SQL or adjust indexes based on findings.

Effect verification : compare performance before and after changes.

Standardization : codify best practices into development guidelines.