MySQL Optimization: Complete Performance Tuning Guide

Slow database queries kill applications. Users leave, servers struggle, and developers scramble. MySQL optimization isn't optional - it's essential for any serious application.

I've optimized MySQL databases for dozens of projects. In my experience, most performance problems come down to a handful of common issues. This guide covers practical MySQL optimization techniques you can apply today - the same techniques I use when a client says "our app is slow."

MySQL Optimization: Understanding Performance

Why do some queries take seconds while others take milliseconds? Before optimizing, understand what makes MySQL slow.

Common Performance Killers

Missing indexes: Full table scans instead of index lookups. The #1 MySQL optimization problem.

Poor query design: Selecting more data than needed, unnecessary JOINs, inefficient WHERE clauses.

Wrong configuration: Default MySQL settings aren't optimized for your workload.

Hardware bottlenecks: Disk I/O, RAM limits, CPU constraints.

Connection overhead: Too many connections, connection pool misconfiguration.

The MySQL Optimization Process

1. Identify slow queries - Use slow query log
2. Analyze query execution - EXPLAIN statement
3. Add appropriate indexes - Based on query patterns
4. Tune configuration - Match your workload
5. Monitor continuously - Performance changes over time

Finding Slow Queries

Enable the Slow Query Log

-- Check current settings
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries taking > 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Log queries not using indexes
SET GLOBAL log_queries_not_using_indexes = 1;

For permanent changes, add to my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

Analyze with mysqldumpslow

# Top 10 slowest queries by time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 by count (most frequent slow queries)
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Queries scanning most rows
mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

Use Performance Schema

-- Enable if not already (MySQL 5.7+)
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE '%statement/%';

-- Find slowest queries
SELECT
    DIGEST_TEXT,
    COUNT_STAR as count,
    ROUND(SUM_TIMER_WAIT/1000000000000, 3) as total_time_sec,
    ROUND(AVG_TIMER_WAIT/1000000000, 3) as avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

EXPLAIN: Understanding Query Execution

EXPLAIN is your primary MySQL optimization tool. It shows how MySQL executes a query.

Basic EXPLAIN Usage

EXPLAIN SELECT * FROM orders
WHERE customer_id = 123
AND status = 'pending';

Output columns that matter:

• type: How MySQL accesses the table (best to worst: const, eq_ref, ref, range, index, ALL)
• key: Which index is used (NULL = no index)
• rows: Estimated rows MySQL will examine
• Extra: Additional information (watch for "Using filesort", "Using temporary")

EXPLAIN Type Values

EXPLAIN ANALYZE (MySQL 8.0+)

Shows actual execution time:

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.created_at > '2024-01-01'
GROUP BY c.id;

Indexing for MySQL Optimization

Proper indexing is the most impactful MySQL optimization technique.

Index Fundamentals

-- Single column index
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Composite index
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Prefix index for long strings
CREATE INDEX idx_users_email_prefix ON users(email(50));

The Left-Prefix Rule

Composite indexes work left to right. With index (a, b, c):

-- Uses index
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- Partially uses index
WHERE a = 1 AND c = 3  -- Only uses 'a'

-- Cannot use index
WHERE b = 2
WHERE b = 2 AND c = 3

Covering Indexes

When an index contains all columns the query needs, MySQL doesn't read the table:

-- Table: orders (id, customer_id, status, total, created_at)
-- Query frequently runs:
SELECT customer_id, status, total FROM orders WHERE customer_id = 123;

-- Covering index:
CREATE INDEX idx_orders_covering ON orders(customer_id, status, total);
-- Now query runs entirely from index

Index Tips for MySQL Optimization

Do index:

• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY
• Columns with high cardinality (many unique values)

Don't index:

• Tables with few rows (< 1000)
• Columns with low cardinality (few unique values)
• Columns rarely used in queries
• Every column - too many indexes slow writes

Analyze Index Usage

-- Find unused 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 = 'your_database';

-- Find missing indexes (frequent full table scans)
SELECT
    object_schema,
    object_name,
    count_read
FROM performance_schema.table_io_waits_summary_by_table
WHERE count_read > 10000
ORDER BY count_read DESC;

Query Optimization Techniques

Select Only What You Need

-- Bad: Fetches all columns
SELECT * FROM orders WHERE customer_id = 123;

-- Good: Only needed columns
SELECT id, status, total FROM orders WHERE customer_id = 123;

Optimize WHERE Clauses

-- Bad: Function on indexed column (can't use index)
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- Good: Range condition (uses index)
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
AND created_at < '2025-01-01';

-- Bad: Leading wildcard (can't use index)
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Good: Trailing wildcard (uses index)
SELECT * FROM users WHERE email LIKE 'john%';

Optimize JOINs

-- Ensure join columns are indexed
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Join on indexed columns
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE c.status = 'active';

-- Avoid joining on functions
-- Bad
ON DATE(o.created_at) = DATE(c.signup_date)

-- Good
ON o.created_at >= c.signup_date
AND o.created_at < c.signup_date + INTERVAL 1 DAY

Limit Results Early

-- Bad: Sorts entire result set
SELECT * FROM products
ORDER BY created_at DESC;

-- Good: Limit before sorting large sets
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20;

Use EXISTS Instead of IN for Large Sets

-- Slower with large subquery
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 1000);

-- Faster
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
    AND o.total > 1000
);

Avoid SELECT DISTINCT When Possible

-- DISTINCT forces sorting/deduplication
SELECT DISTINCT customer_id FROM orders;

-- If you need unique values, GROUP BY can be faster
SELECT customer_id FROM orders GROUP BY customer_id;

-- Or better, fix the root cause of duplicates

MySQL Configuration Optimization

Default MySQL settings are conservative. Tune for your workload.

InnoDB Buffer Pool

The most important MySQL optimization setting. Should be 70-80% of available RAM on dedicated database servers.

[mysqld]
# For 8GB RAM server, use 5-6GB
innodb_buffer_pool_size = 6G

# Multiple instances for better concurrency
innodb_buffer_pool_instances = 8

Check buffer pool efficiency:

SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Calculate hit ratio (should be > 99%)
SELECT
    (1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100 as buffer_pool_hit_ratio;

Query Cache (MySQL 5.7 and earlier)

Query cache is removed in MySQL 8.0. For older versions:

# Usually disable - causes contention at scale
query_cache_type = 0
query_cache_size = 0

Connection Settings

[mysqld]
# Maximum connections
max_connections = 200

# Connection timeout
wait_timeout = 300
interactive_timeout = 300

# Thread cache
thread_cache_size = 50

Logging and Monitoring

[mysqld]
# Slow query logging
slow_query_log = 1
long_query_time = 1

# Error log
log_error = /var/log/mysql/error.log

# Binary log (for replication/recovery)
log_bin = /var/log/mysql/mysql-bin
expire_logs_days = 7

Table Open Cache

[mysqld]
table_open_cache = 4000
table_definition_cache = 2000

Check if cache is adequate:

SHOW STATUS LIKE 'Opened_tables';
-- If high, increase table_open_cache

Recommended my.cnf for MySQL Optimization

For a server with 8GB RAM, mixed read/write workload:

[mysqld]
# Basic settings
user = mysql
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid

# InnoDB settings
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# Connection settings
max_connections = 200
wait_timeout = 300
thread_cache_size = 50

# Table cache
table_open_cache = 4000
table_definition_cache = 2000

# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

# Sorting and joins
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# Binary log
log_bin = /var/log/mysql/mysql-bin
expire_logs_days = 7
binlog_format = ROW

Schema Optimization

Use Appropriate Data Types

-- Bad: Oversized types
price VARCHAR(255)        -- Use DECIMAL
is_active VARCHAR(5)      -- Use TINYINT or BOOLEAN
created_at VARCHAR(50)    -- Use DATETIME

-- Good: Right-sized types
price DECIMAL(10, 2)
is_active TINYINT(1)
created_at DATETIME

-- INT types by size needed
age TINYINT              -- 0 to 255
year SMALLINT            -- -32768 to 32767
user_id INT              -- up to 2 billion
big_counter BIGINT       -- very large numbers

Normalize Then Denormalize Strategically

-- Normalized (good for writes)
SELECT p.name, c.name as category
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.id = 123;

-- Denormalized (good for reads)
-- Add category_name to products table
SELECT name, category_name FROM products WHERE id = 123;

-- Use triggers to keep denormalized data in sync

Partition Large Tables

-- Range partitioning by date
CREATE TABLE orders (
    id INT AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Query only scans relevant partition
SELECT * FROM orders WHERE order_date >= '2024-01-01';

Monitoring MySQL Performance

Essential Metrics to Track

-- Current connections
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Query throughput
SHOW STATUS LIKE 'Questions';
SHOW STATUS LIKE 'Queries';

-- InnoDB metrics
SHOW STATUS LIKE 'Innodb_rows%';

-- Table locks
SHOW STATUS LIKE 'Table_locks%';

Process List

-- See running queries
SHOW PROCESSLIST;

-- Full query text
SHOW FULL PROCESSLIST;

-- Kill long-running query
KILL QUERY process_id;

InnoDB Status

SHOW ENGINE INNODB STATUS\G

Key sections:

• SEMAPHORES: Lock contention
• TRANSACTIONS: Long-running transactions
• BUFFER POOL AND MEMORY: Memory usage
• ROW OPERATIONS: Read/write stats

Monitoring Tools

MySQL Enterprise Monitor: Official tool, requires license

Percona Monitoring and Management (PMM): Free, comprehensive

Prometheus + Grafana: DIY solution with mysqld_exporter

Common MySQL Optimization Scenarios

Scenario 1: Slow Pagination

-- Problem: Slow on large offsets
SELECT * FROM products ORDER BY id LIMIT 100000, 20;

-- Solution: Seek method (keyset pagination)
SELECT * FROM products
WHERE id > 100000
ORDER BY id
LIMIT 20;

Scenario 2: COUNT on Large Tables

-- Problem: COUNT(*) scans entire table
SELECT COUNT(*) FROM orders;

-- Solution 1: Approximate count (fast)
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_NAME = 'orders' AND TABLE_SCHEMA = DATABASE();

-- Solution 2: Counter cache table
CREATE TABLE counters (
    table_name VARCHAR(100) PRIMARY KEY,
    row_count BIGINT
);
-- Update with triggers

Scenario 3: ORDER BY + LIMIT on Large Dataset

-- Problem: Sorts entire result before limiting
SELECT * FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20;

-- Solution: Composite index
CREATE INDEX idx_products_cat_created ON products(category_id, created_at DESC);
-- Now only reads 20 rows from index

Scenario 4: Multiple OR Conditions

-- Problem: OR can prevent index usage
SELECT * FROM products
WHERE brand = 'Apple' OR category = 'Phones';

-- Solution: UNION (if each has index)
SELECT * FROM products WHERE brand = 'Apple'
UNION
SELECT * FROM products WHERE category = 'Phones';

MySQL Optimization Checklist

Query Level

• EXPLAIN shows no full table scans (type != ALL)
• Indexes exist for WHERE, JOIN, ORDER BY columns
• SELECT only needed columns (not *)
• No functions on indexed columns in WHERE
• LIMIT applied for large result sets
• Subqueries converted to JOINs where beneficial

Index Level

• Primary key on every table
• Indexes on foreign keys
• Composite indexes match query patterns
• No duplicate/redundant indexes
• Unused indexes removed

Configuration Level

• innodb_buffer_pool_size set appropriately
• Slow query log enabled
• Connection limits configured
• Table cache sized correctly

Monitoring Level

• Slow query log analyzed regularly
• Key metrics tracked
• Alerts for anomalies
• Regular EXPLAIN review of critical queries

Summary

MySQL optimization is an ongoing process:

1. Enable slow query logging - Know what's slow
2. Use EXPLAIN - Understand query execution
3. Index appropriately - Biggest impact for read-heavy loads
4. Tune configuration - Match server resources
5. Monitor continuously - Performance changes with data growth

Start with the biggest problems first. One well-placed index can improve performance 100x. Configuration changes help, but fixing bad queries matters more.

Related resources:

• Database Design Examples - Good schema design prevents optimization problems

Need help optimizing your MySQL database or solving performance problems? Let's talk about your database challenges.