MySQL Optimization: Complete Performance Tuning Guide

MySQL optimization is the practice of making a MySQL database return queries faster and use server resources more efficiently. It comes down to three things: adding the right indexes, writing efficient queries, and tuning configuration to match your workload.

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

How Do You Find Slow Queries in MySQL?

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;

How Does Indexing Improve MySQL Performance?

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

How Should You Tune MySQL Configuration?

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.

Frequently Asked Questions

What is the most important MySQL optimization?

Adding the right indexes. Missing indexes force full table scans, which are the single most common cause of slow MySQL queries. One well-placed index can improve a query 100x, far more than any configuration change.

How do I find which queries are slow in MySQL?

Enable the slow query log (slow_query_log = 1) with long_query_time set to 1 second, then analyze it with mysqldumpslow. On MySQL 5.7+, the Performance Schema events_statements_summary_by_digest table also ranks queries by total execution time.

What does the EXPLAIN statement do?

EXPLAIN shows how MySQL plans to execute a query - which index it uses, how many rows it expects to examine, and the access type. A type of ALL means a full table scan and signals a missing index; const, eq_ref, and ref indicate efficient index use.

How big should the InnoDB buffer pool be?

On a dedicated database server, set innodb_buffer_pool_size to roughly 70-80% of available RAM. On an 8GB server that is about 5-6GB. The buffer pool hit ratio should stay above 99%; if it drops, the pool is too small for your working set.

Why is my query not using an index?

Common causes are applying a function to an indexed column (YEAR(created_at)), using a leading wildcard in LIKE ('%text'), or violating the left-prefix rule on a composite index. Rewrite the query as a range condition or reorder the index columns so the WHERE clause matches.

Should I use the MySQL query cache?

No. The query cache is removed in MySQL 8.0 and causes lock contention at scale in earlier versions. Disable it (query_cache_type = 0) and rely on the InnoDB buffer pool and proper indexing for read performance instead.

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.