Master MySQL Performance: Full Process for Slow Query Analysis and Index Tuning

Overview

Background

MySQL performance issues often appear gradually; P99 latency can jump from 50 ms to 2 s as traffic and data grow. Slow queries are the most common root cause, and poorly designed indexes are the main source.

Technical features

Slow query log records statements whose execution time exceeds a threshold.

EXPLAIN shows the execution plan, indicating index usage and rows scanned.

Index optimization includes covering indexes, composite indexes, and index condition push‑down (ICP).

Buffer‑pool tuning InnoDB buffer‑pool hit rate directly impacts I/O pressure.

Environment requirements

MySQL 8.4.x LTS (optimizer improvements)

pt‑query‑digest 3.5+ (Percona Toolkit)

Linux (pt‑query‑digest depends on Perl)

Privileges: PROCESS, SELECT (required for analyzing slow queries and execution plans)

Detailed steps

Slow query log configuration

Enable slow query log

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1   # record queries >1 s; early production may set 0.5
log_queries_not_using_indexes = OFF   # not recommended; generates noise
log_slow_extra = ON   # MySQL 8.0.14+; records more context
min_examined_row_limit = 100   # ignore queries scanning fewer than 100 rows

Dynamic modification (no restart required):

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_slow_extra = ON;

pt‑query‑digest analysis

# Install Percona Toolkit
apt install percona-toolkit   # Ubuntu
yum install percona-toolkit   # CentOS

# Basic analysis: sort by total execution time, output Top 10 slow queries
pt-query-digest /var/log/mysql/slow.log \
  --limit 10 \
  --order-by Query_time:sum \
  > /tmp/slow_report.txt

# Analyze only the last hour
pt-query-digest /var/log/mysql/slow.log \
  --since "1h" \
  --limit 20

# Filter a specific database
pt-query-digest /var/log/mysql/slow.log \
  --filter '$event->{db} eq "production_db"'

# Store results in MySQL tables for historical comparison
pt-query-digest /var/log/mysql/slow.log \
  --review h=127.0.0.1,D=percona,t=query_review \
  --history h=127.0.0.1,D=percona,t=query_history \
  --no-report

Key metric: Rows examine / Rows sent. A ratio > 100 indicates low index efficiency.

EXPLAIN execution‑plan interpretation

Core fields

EXPLAIN SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20\G

Sample output (excerpt):

id: 1
select_type: SIMPLE
table: o
type: range        ← key field
possible_keys: idx_status_created,idx_created_at
key: idx_status_created
key_len: 10
ref: NULL
rows: 1250        ← estimated rows scanned
filtered: 100.00
Extra: Using index condition; Using filesort

type field (best → worst)

system – table has only one row (optimal)

const – primary‑key or unique‑index equality (excellent)

eq_ref – JOIN uses primary/unique key (very good)

ref – non‑unique index equality (good)

range – index range scan (acceptable)

index – full index scan (poor)

ALL – full table scan (worst, must be optimized)

Extra field key information Using index – covering index, no row lookup. Using index condition – index condition push‑down, filters at the index level. Using filesort – additional sorting; can be very slow on large data sets. Using temporary – temporary table created for GROUP BY or ORDER BY. Using where – server‑side filter; index does not fully cover WHERE clause.

EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 12345 AND status = 'paid'\G
-- Output includes real execution time and row count
-- → Filter: (orders.status = 'paid') (cost=5.25 rows=3) (actual time=0.045..0.089 rows=2 loops=1)
-- → Index lookup on orders using idx_user_id (user_id=12345) (cost=3.50 rows=15) (actual time=0.038..0.075 rows=15 loops=1)

If the gap between actual rows and rows (estimated) is large, statistics are stale and ANALYZE TABLE should be run.

Index design principles

Composite index left‑most prefix

-- Assume composite index idx_user_status_created (user_id, status, created_at)
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01';
-- Queries that skip the leftmost column cannot use the index:
SELECT * FROM orders WHERE status = 'paid';
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2024-01-01';
-- Recommendation: put equality columns first, range column last

Covering index

-- Original query that requires a table lookup
SELECT id, amount, created_at FROM orders
WHERE user_id = 1 AND status = 'paid';

-- Create covering index that includes all needed columns
ALTER TABLE orders ADD INDEX idx_covering (user_id, status, amount, created_at);
-- EXPLAIN now shows "Using index" – no table lookup needed
-- For high‑frequency queries, covering index can drop response time from milliseconds to microseconds

Covering indexes increase index size and write cost; avoid overusing them on write‑heavy tables.

Index condition push‑down (ICP)

-- Composite index idx_age_name (age, name)
-- Query: WHERE age > 20 AND name LIKE 'Zhang%'

-- Without ICP:
-- 1. Engine uses age > 20 to find rows
-- 2. Full rows are fetched (table lookup)
-- 3. Server filters name LIKE 'Zhang%'

-- With ICP (default in MySQL 5.6+):
-- 1. Engine uses age > 20 to find index entries
-- 2. Index layer directly checks name LIKE 'Zhang%'
-- 3. Only matching rows go to table lookup
-- EXPLAIN Extra shows "Using index condition"

-- Verify ICP is enabled (normally on)
SET optimizer_switch = 'index_condition_pushdown=on';

InnoDB buffer‑pool tuning

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Buffer pool size: 50‑75% of physical memory
# Example: 16 GB server → set 10‑12 GB
innodb_buffer_pool_size = 10G

# Number of buffer‑pool instances: each instance has its own lock
# Recommendation: 1‑2 GB per instance, e.g., 8 instances for a 10 GB pool
innodb_buffer_pool_instances = 8

# Warm‑up: automatically load hot data after restart
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25   # dump only the hottest 25%

# Monitor buffer‑pool hit rate
# hit_rate = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

-- View buffer‑pool hit rate
SELECT FORMAT(
    (1 - (variable_value / (
        SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests'
    )))*100, 2) AS hit_rate_pct
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';
-- If hit rate < 95%, consider increasing the buffer pool
-- View detailed buffer‑pool statistics
SELECT pool_id, pool_size, free_buffers, database_pages, hit_rate
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Connection‑pool configuration

[mysqld]
max_connections = 500          # set according to concurrency
thread_cache_size = 50
wait_timeout = 600
interactive_timeout = 600
max_connect_errors = 100
back_log = 128

-- Monitor connection status
SHOW STATUS LIKE 'Threads_%';
-- Threads_connected: current connections
-- Threads_running: active threads actually executing SQL
-- Threads_cached: cached threads
-- If Threads_running stays close to max_connections, there is connection backlog

SELECT user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 20;

Example: production case from slow query to index optimization

Case background

e‑commerce orders table with 50 million rows experienced a response‑time increase from 200 ms to 8 s.

Locate slow queries

# Analyze slow queries from the last 30 minutes
pt-query-digest /var/log/mysql/slow.log \
  --since "30m" \
  --order-by Query_time:sum \
  --limit 5

Most time‑consuming SQL:

SELECT o.id, o.order_no, o.amount, o.status, o.created_at,
       u.name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.merchant_id = 1001
  AND o.status IN ('pending','paid')
  AND o.created_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY o.created_at DESC
LIMIT 20 OFFSET 0;
-- Average execution time 6.8 s, scanning 3.2 million rows

Analyze execution plan

EXPLAIN SELECT o.id, o.order_no, o.amount, o.status, o.created_at,
       u.name, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.merchant_id = 1001
  AND o.status IN ('pending','paid')
  AND o.created_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY o.created_at DESC
LIMIT 20\G

Result:

type: ALL, rows: 50000000, Extra: Using where; Using filesort

Full table scan + filesort identified as the problem.

Index design decision

-- Evaluate column selectivity
SELECT
  COUNT(DISTINCT merchant_id)/COUNT(*) AS merchant_selectivity,
  COUNT(DISTINCT status)/COUNT(*) AS status_selectivity,
  COUNT(DISTINCT DATE(created_at))/COUNT(*) AS date_selectivity
FROM orders;
-- merchant_selectivity: 0.0002 (low)
-- status_selectivity: 0.00000012 (very low)
-- date_selectivity: 0.0006 (low)

-- Create composite index: equality columns first, range column last
ALTER TABLE orders ADD INDEX idx_merchant_status_created (merchant_id, status, created_at);

-- Optional covering index for orders columns
ALTER TABLE orders ADD INDEX idx_merchant_status_created_cover (
    merchant_id, status, created_at, id, order_no, amount, user_id);

Verify optimization effect

EXPLAIN SELECT o.id, o.order_no, o.amount, o.status, o.created_at,
       u.name, u.phone
FROM orders o FORCE INDEX (idx_merchant_status_created)
JOIN users u ON o.user_id = u.id
WHERE o.merchant_id = 1001
  AND o.status IN ('pending','paid')
  AND o.created_at BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY o.created_at DESC
LIMIT 20\G
-- type: range, rows: 1250, Extra: Using index condition
-- Scanned rows reduced from 50 M to 1 250, response time down to ~15 ms

Histogram statistics (MySQL 8.0+)

-- Build histogram for low‑selectivity columns
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, merchant_id WITH 256 BUCKETS;

-- View histogram information
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE table_name = 'orders'\G

Histograms help the optimizer estimate rows for columns that are not indexed but have low selectivity.

Best practices and caveats

Index design principles

Prioritize high‑selectivity columns; place equality columns before range columns in composite indexes.

Limit the number of indexes per table (generally ≤ 5; stricter for write‑heavy tables).

Periodically drop unused indexes. Example query to find never‑used indexes:

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql','performance_schema','information_schema')
ORDER BY object_schema, object_name;

SQL writing guidelines

-- Avoid functions on indexed columns
-- Wrong:
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Correct:
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Avoid implicit type conversion
-- Wrong:
SELECT * FROM users WHERE phone = 13800138000;
-- Correct:
SELECT * FROM users WHERE phone = '13800138000';

-- Large OFFSET is slow
-- Wrong:
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- Correct (cursor pagination):
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 20;

Regular maintenance

-- Update statistics after >10% data change
ANALYZE TABLE orders;

-- Rebuild indexes when fragmentation is high
SELECT table_name,
       ROUND(data_free/(data_length+index_length)*100,2) AS frag_pct
FROM information_schema.TABLES
WHERE table_schema = 'production_db'
  AND data_free > 0
ORDER BY frag_pct DESC;

-- Online rebuild (MySQL 8.0+, no table lock)
ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

Common pitfalls

SELECT *

disables covering indexes; list required columns explicitly.

Adding an index with ALTER TABLE ADD INDEX locks the table; use pt-online-schema-change or gh‑ost for online changes. FORCE INDEX should be used only for temporary debugging.

Typical errors and solutions

Index exists but not used → stale statistics; run ANALYZE TABLE.

Adding an index makes query slower → low selectivity; drop the index or use a covering index.

ORDER BY triggers filesort → sorting column not in index or order mismatched; adjust composite index column order.

JOIN performance poor → join column on driven table lacks an index; create an index on that column.

Deep pagination extremely slow → large OFFSET scans many rows; use cursor‑based pagination.

Fault diagnosis and monitoring

Real‑time performance diagnosis

-- View currently running slow SQL (>5 s)
SELECT id, user, host, db, time, state,
       LEFT(info,200) AS sql_snippet
FROM information_schema.PROCESSLIST
WHERE command = 'Query' AND time > 5
ORDER BY time DESC;

-- View lock‑wait situation
SELECT r.trx_id AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM information_schema.INNODB_TRX r
JOIN information_schema.INNODB_TRX b
  ON r.trx_wait_started IS NOT NULL
 AND b.trx_id = (
   SELECT blocking_trx_id
   FROM performance_schema.data_lock_waits
   WHERE requesting_engine_transaction_id = r.trx_id
   LIMIT 1
 );

Performance monitoring metrics

# Real‑time MySQL status monitoring
mysqladmin -u root -p extended-status -i 1 | grep -E "Questions|Slow|Threads_running|InnoDB_buffer"

Key indicators:

Buffer‑pool hit rate > 99 % (alert < 95 %).

Slow queries per second < 1 (alert > 10).

Threads_running < 20 (alert > 50).

InnoDB row‑lock waits < 5/s (alert > 50/s).

Questions per second > 2× baseline may indicate sudden load.

Conclusion

Technical takeaways

Slow‑query identification: pt‑query‑digest sorts by total cost; Rows examine / Rows sent > 100 signals need for optimization.

EXPLAIN interpretation: improve type from ALL to range or ref, eliminate Using filesort and Using temporary.

Index design: place equality columns first, range columns last; covering indexes remove table lookups.

Buffer‑pool: hit rate below 95 % mandates expansion; warm‑up after restart avoids cold‑start latency.

Advanced learning directions

Deep use of Performance Schema for fine‑grained metrics.

Query rewrite plugins such as ProxySQL’s query_rewrite.

Partitioned tables for tables exceeding 100 M rows, using time‑based partitioning to reduce scan ranges.

References

MySQL 8.4 Optimizer Documentation

Percona Toolkit Documentation

"Use The Index, Luke" – classic index‑learning resource