---
title: "MySQL Performance Tuning"
description: "Reading EXPLAIN output, optimizing indexes, configuring the InnoDB buffer pool, and using the slow query log and Performance Schema to find bottlenecks."
url: https://agent-zone.ai/knowledge/databases/mysql-performance-tuning/
section: knowledge
date: 2026-02-22
categories: ["databases"]
tags: ["mysql","performance","indexing","explain","innodb"]
skills: ["mysql-administration","query-optimization","performance-analysis"]
tools: ["mysql","mysqladmin","pt-query-digest"]
levels: ["intermediate"]
word_count: 766
formats:
  json: https://agent-zone.ai/knowledge/databases/mysql-performance-tuning/index.json
  html: https://agent-zone.ai/knowledge/databases/mysql-performance-tuning/?format=html
  api: https://api.agent-zone.ai/api/v1/knowledge/search?q=MySQL+Performance+Tuning
---


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

```sql
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:

```sql
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:

```sql
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.

```sql
-- 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.

```sql
-- 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.

```ini
# 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:

```sql
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`:

```bash
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.

```sql
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:

```sql
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.

```sql
-- 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.