MySQL Performance Tuning#

Performance tuning comes down to three things: making queries touch fewer rows (indexes), keeping hot data in memory (buffer pool), and finding the slow queries (slow query log, Performance Schema).

Reading EXPLAIN Output#

EXPLAIN shows MySQL’s query execution plan. Always use EXPLAIN ANALYZE (MySQL 8.0.18+) for actual runtime stats, not just estimates.

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id;

Key columns:

  • type: Join type, best to worst: const > eq_ref > ref > range > index > ALL. ALL = full table scan.
  • key: Index chosen. NULL = no index used.
  • rows: Estimated rows to examine. Lower is better.
  • Extra: Using index (covering index, good), Using temporary (temp table, bad), Using filesort (expensive sort).

Example of a problematic plan:

EXPLAIN SELECT * FROM orders WHERE customer_email = 'alice@example.com';
-- type: ALL, rows: 4500000, Extra: Using where
-- Full table scan on 4.5 million rows. Needs an index.

After adding an index:

CREATE INDEX idx_orders_email ON orders(customer_email);

EXPLAIN SELECT * FROM orders WHERE customer_email = 'alice@example.com';
-- type: ref, key: idx_orders_email, rows: 3, Extra: NULL

Index Optimization#

Composite Indexes#

MySQL uses composite indexes left-to-right. An index on (a, b, c) supports queries filtering on (a), (a, b), and (a, b, c), but not (b) or (b, c) alone.

-- This index supports all three query patterns
CREATE INDEX idx_user_status_date ON orders(user_id, status, created_at);

-- Uses index: filters on user_id (leftmost)
SELECT * FROM orders WHERE user_id = 42;

-- Uses index: filters on user_id + status
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

-- Uses index: filters on user_id + status + range on created_at
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped'
  AND created_at > '2025-06-01';

-- Does NOT use this index: skips the leftmost column
SELECT * FROM orders WHERE status = 'shipped';

Covering Indexes#

A covering index contains all columns the query needs, so MySQL never reads the actual table rows. This shows as Using index in EXPLAIN.

-- Query only needs user_id, status, and created_at
CREATE INDEX idx_covering ON orders(user_id, status, created_at);

EXPLAIN SELECT status, created_at FROM orders WHERE user_id = 42;
-- Extra: Using index (covering index -- no table lookup needed)

Index Condition Pushdown (ICP)#

ICP pushes WHERE conditions to the storage engine, filtering during the index scan instead of after. It shows as Using index condition in EXPLAIN. Enabled by default in 8.x – no action needed, but knowing the term helps when reading plans.

Slow Query Log#

The slow query log captures queries exceeding a time threshold.

# my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # seconds (can be fractional: 0.5)
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000 # skip trivial queries

Enable dynamically without restart:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 1;

Analyze the slow log with Percona’s pt-query-digest:

pt-query-digest /var/log/mysql/slow.log
# Groups similar queries, ranks by total time, shows EXPLAIN-ready fingerprints

This outputs a ranked list of query patterns by total execution time. Focus on the top 3-5 – they account for most of the load.

InnoDB Buffer Pool Tuning#

The buffer pool is InnoDB’s main memory cache for data and index pages. Its size is the single most important performance variable.

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';  -- logical reads (from cache)
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';           -- physical reads (from disk)
-- Hit ratio = 1 - (reads / read_requests). Should be > 99%.

If the hit ratio is below 99%, your buffer pool is too small. Increase innodb_buffer_pool_size. On MySQL 8.x, you can resize dynamically:

SET GLOBAL innodb_buffer_pool_size = 6 * 1024 * 1024 * 1024;  -- 6 GB
-- Resizing happens in chunks (innodb_buffer_pool_chunk_size, default 128 MB)

Connection Management#

Each connection consumes 1-10 MB of memory. Monitor with SHOW STATUS LIKE 'Max_used_connections' and compare against max_connections. Use connection pooling (ProxySQL, HikariCP) rather than raising max_connections. A well-tuned pool of 20-50 connections handles more load than 500 idle connections.

Query Cache (Removed in 8.0)#

MySQL’s query cache was removed entirely in MySQL 8.0. If you are migrating from 5.7 and your config still references query_cache_type or query_cache_size, remove those lines. They will cause startup warnings or errors. For application-level caching, use Redis or Memcached instead.

Performance Schema Essentials#

Performance Schema is MySQL’s built-in instrumentation, enabled by default in 8.x with negligible overhead.

-- Top queries by total execution time
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e12 AS total_seconds,
       AVG_TIMER_WAIT/1e12 AS avg_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

This is the authoritative source for understanding where MySQL spends its time.