Master MySQL Performance: From Slow Query Analysis to Index Tuning

Overview

MySQL performance degrades gradually as traffic and data volume increase, eventually causing response times to jump from milliseconds to seconds. The most common cause is slow queries, which are usually the result of sub‑optimal index design.

Key Technical Features

Slow query log : records any query whose execution time exceeds a configurable threshold; it is the starting point for performance analysis.

EXPLAIN : shows the execution plan, indicating whether an index is used and how many rows are scanned.

Index optimization : covering indexes, composite (multi‑column) indexes, and index‑condition pushdown (ICP) are the three core techniques.

InnoDB buffer pool : the hit rate directly impacts I/O pressure.

Environment Requirements

MySQL 8.4.x LTS (optimizer improvements)

Percona Toolkit pt‑query‑digest 3.5+ (requires Perl)

Linux OS

PROCESS and SELECT privileges for slow‑query and EXPLAIN analysis

Step‑by‑Step Procedure

1. Enable Slow Query Logging

# /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 (0.5 s in early production)
log_queries_not_using_indexes = OFF   # avoid noisy logs
log_slow_extra = ON   # MySQL 8.0.14+ records extra context
min_examined_row_limit = 100   # ignore tiny scans

Dynamic changes (no restart required):

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

2. Analyze Logs with pt‑query‑digest

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

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

# Recent 1‑hour queries
pt-query-digest /var/log/mysql/slow.log \
  --since "1h" --limit 20

# Filter by 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 to watch: Rows examine / Rows sent. If the ratio exceeds 100, the index is ineffective.

3. Interpret EXPLAIN Output

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;

Typical fields:

type (system, const, eq_ref, ref, range, index, ALL) – from best to worst.

key – the actual index used.

rows – estimated rows scanned.

Extra – e.g., Using index (covering), Using index condition (ICP), Using filesort, Using temporary, Using where.

3.1 EXPLAIN ANALYZE (MySQL 8.0.18+)

-- Executes the query and shows real runtime
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid'\G

Compare actual rows with the estimated rows; large gaps indicate stale statistics and require ANALYZE TABLE.

4. Index Design Principles

4.1 Left‑most Prefix

-- Composite index (user_id, status, created_at)
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01';

-- Bad: missing leading column
SELECT * FROM orders WHERE status = 'paid' AND created_at > '2024-01-01';

4.2 Covering Index

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

-- Add covering index
ALTER TABLE orders ADD INDEX idx_covering (user_id, status, amount, created_at);

When EXPLAIN shows Using index, the query avoids a table lookup.

4.3 Index Condition Pushdown (ICP)

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

ICP filters rows at the storage‑engine level, reducing the number of rows returned to the server.

5. InnoDB Buffer Pool Tuning

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
innodb_buffer_pool_size = 10G   # 50‑75% of physical RAM
innodb_buffer_pool_instances = 8   # one instance per 1‑2 GB
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25   # dump hottest 25%

Monitor hit rate (should be >99 %; alert <95 %):

SELECT FORMAT(1 - (variable_value / (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')), 2) * 100 AS hit_rate_pct
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

6. Connection‑Pool Configuration

[mysqld]
max_connections = 500
thread_cache_size = 50
wait_timeout = 600
interactive_timeout = 600
max_connect_errors = 100
back_log = 128

Monitor connection health:

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

Practical Example: E‑commerce Orders Table (50 M rows)

Problem

Response time for the order list page jumped from 200 ms to 8 s.

Step 1 – Locate Slow Query

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

Identified query:

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;
-- Avg exec time 6.8 s, scanned 3.2 M rows

Step 2 – Analyze Execution Plan

EXPLAIN SELECT ...

Result: type: ALL, rows: 50000000, Extra: Using where; Using filesort – full table scan.

Step 3 – Design Index

-- Evaluate column selectivity
SELECT COUNT(DISTINCT merchant_id)/COUNT(*) AS merchant_sel,
       COUNT(DISTINCT status)/COUNT(*) AS status_sel,
       COUNT(DISTINCT DATE(created_at))/COUNT(*) AS date_sel
FROM orders;

-- Composite index (merchant_id, status, created_at)
ALTER TABLE orders ADD INDEX idx_merchant_status_created (merchant_id, status, created_at);

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

Step 4 – Verify Improvement

EXPLAIN SELECT ... FORCE INDEX (idx_merchant_status_created);
-- New plan: type: range, rows: 1250, Extra: Using index condition
-- Scan rows reduced from 50 M to 1.25 K, response time ~15 ms

Histogram Statistics (MySQL 8.0+)

ANALYZE TABLE orders UPDATE HISTOGRAM ON status, merchant_id WITH 256 BUCKETS;
SELECT * FROM information_schema.COLUMN_STATISTICS WHERE table_name = 'orders'\G

Use histograms for low‑selectivity columns that are not indexed.

Best Practices & Cautions

Index Design

Prioritize high‑cardinality columns; place equality conditions before range conditions.

Limit total indexes per table (≤ 5); especially for write‑heavy tables.

Periodically drop unused indexes (query performance_schema.table_io_waits_summary_by_index_usage).

SQL Coding Guidelines

Avoid functions on indexed columns (e.g., YEAR(created_at)).

Specify columns explicitly; avoid SELECT * which disables covering indexes.

Prevent implicit type conversion (match data types exactly).

Use keyset pagination instead of large OFFSET values.

Regular Maintenance

Run ANALYZE TABLE after >10 % data change.

Rebuild fragmented indexes (check data_free/(data_length+index_length)).

Online index rebuild on MySQL 8.0+:

ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;

Fault Diagnosis & Monitoring

Real‑time Diagnostics

-- Long‑running queries (>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;

-- Lock wait analysis
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);

Key Monitoring Metrics

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).

QPS baseline; alert on 2× increase.

Backup & Log Rotation

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    missingok
    notifempty
    postrotate
        mysql -u root -p"${MYSQL_ROOT_PASS}" -e "FLUSH SLOW LOGS;"
    endscript
}

Technical Takeaways

Use pt‑query‑digest to pinpoint high‑cost queries; focus on Rows examine / Rows sent > 100.

Improve EXPLAIN types from ALL to range / ref and eliminate Using filesort and Using temporary.

Design composite indexes with equality columns first, range columns last; employ covering indexes to avoid table lookups.

Maintain buffer‑pool size so hit rate stays > 95 %; increase instances to reduce lock contention.