MySQL Performance Optimization: Complete Guide (2025)

MySQL powers millions of applications worldwide, but achieving optimal performance requires careful tuning and optimization. This guide covers everything from basic configuration to advanced optimization techniques for MySQL 8.0 and beyond.

Table of Contents

1. Configuration Tuning
2. Query Optimization
3. Indexing Best Practices
4. InnoDB Optimization
5. Caching Strategies
6. Connection Management
7. Monitoring and Profiling

Configuration Tuning

Essential my.cnf Settings

Memory Configuration

# Memory Settings (for 16GB RAM server)

[mysqld]

# InnoDB buffer pool - 70-80% of available RAM for dedicated DB server
innodb_buffer_pool_size = 12G

# Buffer pool instances (1GB per instance recommended)
innodb_buffer_pool_instances = 12

# Maximum connections
max_connections = 200

# Thread cache (reduce thread creation overhead)
thread_cache_size = 50

# Query cache (DEPRECATED in MySQL 8.0+)
# query_cache_type = 1
# query_cache_size = 64M

# Table cache
table_open_cache = 4000
table_definition_cache = 2000

# Sort and join buffers
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M

# Temporary tables
tmp_table_size = 64M
max_heap_table_size = 64M

Key Guidelines:

• innodb_buffer_pool_size: Most critical setting - 70-80% of RAM for dedicated servers
• max_connections: Set based on expected concurrent connections
• sort_buffer_size: Per-connection allocation; keep reasonable (2-4MB)
• tmp_table_size: Increase if many temporary tables created on disk

InnoDB Settings

# InnoDB Configuration

# Log file size (larger = better write performance, slower recovery)
innodb_log_file_size = 1G

# Log buffer
innodb_log_buffer_size = 32M

# Flush method (O_DIRECT for Linux, best performance)
innodb_flush_method = O_DIRECT

# I/O capacity (SSD: 5000-20000, HDD: 200)
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# File per table (recommended)
innodb_file_per_table = 1

# Flush log at transaction commit
innodb_flush_log_at_trx_commit = 1  # Safest (default)
# innodb_flush_log_at_trx_commit = 2  # Better performance, slight risk

# Adaptive hash index
innodb_adaptive_hash_index = ON

# Thread concurrency (0 = auto)
innodb_thread_concurrency = 0

# Read/write threads
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# NUMA support (for NUMA systems)
innodb_numa_interleave = ON

Binary Logging and Replication

# Binary Logging

# Enable binary logging
log_bin = /var/log/mysql/mysql-bin.log

# Binary log format (ROW is recommended)
binlog_format = ROW

# Binary log expiration (days)
expire_logs_days = 7
binlog_expire_logs_seconds = 604800  # MySQL 8.0+

# Max binary log size
max_binlog_size = 1G

# Sync binary log (1 = safest, 0 = fastest)
sync_binlog = 1

Query and Slow Log Settings

# Logging Configuration

# Slow query log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2  # Queries taking > 2 seconds

# Log queries not using indexes
log_queries_not_using_indexes = 1

# Throttle log_queries_not_using_indexes
log_throttle_queries_not_using_indexes = 10

# General query log (use sparingly - high overhead)
general_log = 0
general_log_file = /var/log/mysql/general.log

Query Optimization

Using EXPLAIN

-- Basic EXPLAIN
EXPLAIN SELECT * FROM users WHERE email = 'user@example.com';

-- Extended information (MySQL 8.0+)
EXPLAIN FORMAT=TREE
SELECT u.*, o.order_total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01';

-- JSON format for detailed analysis
EXPLAIN FORMAT=JSON
SELECT * FROM products WHERE category = 'electronics';

-- EXPLAIN ANALYZE (MySQL 8.0.18+) - actually runs query
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);

Reading EXPLAIN Output

Key Columns:

• id: Query identifier
• select_type: SIMPLE, SUBQUERY, DERIVED, etc.
• type: Join type (system > const > eq_ref > ref > range > index > ALL)
• possible_keys: Indexes that could be used
• key: Index actually used
• rows: Estimated rows examined
• Extra: Additional information

Join Types (Best to Worst):

1. system: Table has only one row
2. const: Primary key or unique index lookup
3. eq_ref: One row read per previous table combination
4. ref: Non-unique index lookup
5. range: Index range scan
6. index: Full index scan
7. ALL: Full table scan (usually bad for large tables)

Query Optimization Techniques

1. Select Only Required Columns

-- Bad - retrieves all columns
SELECT * FROM users WHERE id = 123;

-- Good - select only needed columns
SELECT id, email, name FROM users WHERE id = 123;

-- Avoid SELECT * in joins
-- Bad
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;

-- Good
SELECT u.id, u.name, o.order_number, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

2. Use WHERE Clause Effectively

-- Bad - function on indexed column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2025;

-- Good - range query allows index use
SELECT * FROM users
WHERE created_at >= '2025-01-01'
  AND created_at < '2026-01-01';

-- Bad - leading wildcard prevents index use
SELECT * FROM users WHERE email LIKE '%@example.com';

-- Good - can use index
SELECT * FROM users WHERE email LIKE 'john%';

3. Optimize JOINs

-- Ensure JOIN columns are indexed
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);  -- Usually PK already

-- Use INNER JOIN when possible (faster than LEFT JOIN)
-- Bad - unnecessary LEFT JOIN
SELECT u.name, o.order_number
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NOT NULL;

-- Good - INNER JOIN when you need matching rows
SELECT u.name, o.order_number
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Join order matters - smaller result sets first
SELECT *
FROM (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
) recent_orders
JOIN users ON recent_orders.user_id = users.id;

4. Optimize Subqueries

-- Bad - correlated subquery
SELECT * FROM users u
WHERE (
    SELECT COUNT(*) FROM orders o
    WHERE o.user_id = u.id
) > 5;

-- Good - use JOIN instead
SELECT u.*
FROM users u
JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) > 5
) o ON u.id = o.user_id;

-- Or use EXISTS for existence checks
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id
    LIMIT 1
);

5. Use LIMIT for Large Result Sets

-- Always use LIMIT when possible
SELECT * FROM logs
WHERE created_at > '2025-01-01'
ORDER BY created_at DESC
LIMIT 100;

-- Use pagination efficiently
-- Bad - OFFSET becomes slow with large offsets
SELECT * FROM products
ORDER BY id
LIMIT 100 OFFSET 10000;

-- Good - use WHERE clause with last seen ID
SELECT * FROM products
WHERE id > 10100
ORDER BY id
LIMIT 100;

6. Optimize GROUP BY and ORDER BY

-- Ensure columns used in GROUP BY/ORDER BY are indexed
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- This query can use the index efficiently
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
ORDER BY user_id;

-- Avoid filesort with proper index
EXPLAIN SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10;
-- Check Extra column - avoid "Using filesort"

7. Use Prepared Statements

-- Prepared statements improve performance for repeated queries
PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?';
SET @email = 'user@example.com';
EXECUTE stmt USING @email;
DEALLOCATE PREPARE stmt;

-- In application code (PHP example)
$stmt = $mysqli->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email);
$stmt->execute();

Indexing Best Practices

When to Create Indexes

Create indexes for:

• Primary keys (automatic)
• Foreign keys
• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY and GROUP BY
• Columns with high selectivity

Avoid indexes when:

• Table is very small
• Column has low cardinality (few distinct values)
• High write, low read workload
• Column rarely used in queries

Index Types

Primary Key Index

-- Automatic with PRIMARY KEY
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL
);

-- Clustered index - data stored in PK order

Unique Index

-- Enforce uniqueness and improve lookups
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Or with constraint
ALTER TABLE users ADD CONSTRAINT uk_email UNIQUE (email);

Regular (Non-unique) Index

-- Single column index
CREATE INDEX idx_orders_status ON orders(status);

-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Can be used for:
-- WHERE user_id = X
-- WHERE user_id = X AND status = Y
-- Cannot efficiently be used for:
-- WHERE status = Y (alone)

Full-text Index

-- Create full-text index
CREATE FULLTEXT INDEX idx_articles_content ON articles(title, content);

-- Search using MATCH AGAINST
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('mysql performance' IN NATURAL LANGUAGE MODE);

-- Boolean mode for more control
SELECT * FROM articles
WHERE MATCH(title, content) AGAINST('+mysql -oracle' IN BOOLEAN MODE);

Spatial Index

-- For geometric data (requires SPATIAL column type)
CREATE TABLE stores (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    location POINT NOT NULL,
    SPATIAL INDEX idx_location (location)
);

-- Query within distance
SELECT * FROM stores
WHERE ST_Distance_Sphere(location, POINT(-122.4194, 37.7749)) < 5000;

Prefix Index

-- Index first N characters (saves space)
CREATE INDEX idx_users_email_prefix ON users(email(20));

-- Useful for long VARCHAR/TEXT columns
CREATE INDEX idx_articles_content_prefix ON articles(content(100));

Index Optimization

-- Find duplicate/redundant indexes
SELECT
    table_name,
    GROUP_CONCAT(index_name ORDER BY index_name) as indexes,
    GROUP_CONCAT(column_name ORDER BY column_name) as columns
FROM information_schema.statistics
WHERE table_schema = 'mydb'
GROUP BY table_name, column_name
HAVING COUNT(*) > 1;

-- Find unused indexes
SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    s.INDEX_NAME,
    s.COLUMN_NAME
FROM information_schema.TABLES t
LEFT JOIN information_schema.STATISTICS s
    ON t.TABLE_SCHEMA = s.TABLE_SCHEMA
    AND t.TABLE_NAME = s.TABLE_NAME
WHERE t.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema')
    AND s.INDEX_NAME IS NOT NULL
    AND s.INDEX_NAME != 'PRIMARY'
    AND s.TABLE_NAME NOT IN (
        SELECT DISTINCT table_name
        FROM performance_schema.table_io_waits_summary_by_index_usage
        WHERE index_name IS NOT NULL
    );

-- Analyze table to update index statistics
ANALYZE TABLE users;

-- Check index cardinality
SHOW INDEX FROM users;

-- Rebuild index (optimize table)
OPTIMIZE TABLE users;

InnoDB Optimization

Buffer Pool Optimization

-- Check buffer pool usage
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Important metrics:
-- Innodb_buffer_pool_reads: Disk reads
-- Innodb_buffer_pool_read_requests: Logical reads
-- Buffer pool hit ratio = (read_requests - reads) / read_requests
-- Target > 99%

-- Calculate hit ratio
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;

Monitoring InnoDB Status

-- Detailed InnoDB status
SHOW ENGINE INNODB STATUS;

-- Key sections:
-- BACKGROUND THREAD: I/O and purge operations
-- SEMAPHORES: Wait conditions
-- TRANSACTIONS: Active transactions
-- BUFFER POOL AND MEMORY: Memory usage
-- ROW OPERATIONS: Insert/update/delete stats

InnoDB Table Optimization

-- Defragment table (reclaim space)
OPTIMIZE TABLE users;

-- Analyze table
ANALYZE TABLE users;

-- Check table for errors
CHECK TABLE users;

-- Repair table (MyISAM only, InnoDB auto-repairs)
REPAIR TABLE users;

-- Monitor table size and fragmentation
SELECT
    table_name,
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)",
    ROUND((data_free / 1024 / 1024), 2) AS "Free (MB)",
    ROUND((data_free / (data_length + index_length)) * 100, 2) AS "Fragmentation %"
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
    AND (data_free / (data_length + index_length)) > 0.1
ORDER BY (data_free / (data_length + index_length)) DESC;

Caching Strategies

MySQL Query Cache (Deprecated in 8.0)

-- Note: Query cache removed in MySQL 8.0
-- Use application-level caching instead (Redis, Memcached)

-- For MySQL 5.7 and earlier:
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

-- Monitor query cache
SHOW STATUS LIKE 'Qcache%';

Application-Level Caching

# Python example with Redis
import redis
import mysql.connector

redis_client = redis.Redis(host='localhost', port=6379, db=0)

def get_user(user_id):
    # Try cache first
    cache_key = f"user:{user_id}"
    cached = redis_client.get(cache_key)

    if cached:
        return json.loads(cached)

    # Cache miss - query database
    db = mysql.connector.connect(host='localhost', user='user', password='pass', database='mydb')
    cursor = db.cursor(dictionary=True)
    cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
    user = cursor.fetchone()

    # Store in cache (expire after 1 hour)
    if user:
        redis_client.setex(cache_key, 3600, json.dumps(user))

    return user

Result Set Caching

-- Use materialized views pattern
CREATE TABLE daily_stats (
    report_date DATE PRIMARY KEY,
    total_orders INT,
    total_revenue DECIMAL(10,2),
    updated_at TIMESTAMP
);

-- Populate with scheduled job
INSERT INTO daily_stats (report_date, total_orders, total_revenue, updated_at)
SELECT
    DATE(created_at),
    COUNT(*),
    SUM(total),
    NOW()
FROM orders
WHERE DATE(created_at) = CURDATE()
ON DUPLICATE KEY UPDATE
    total_orders = VALUES(total_orders),
    total_revenue = VALUES(total_revenue),
    updated_at = VALUES(updated_at);

Connection Management

Connection Pooling

# Python with connection pooling
from mysql.connector import pooling

connection_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=10,
    pool_reset_session=True,
    host='localhost',
    database='mydb',
    user='myuser',
    password='mypass'
)

# Get connection from pool
connection = connection_pool.get_connection()
try:
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users")
    results = cursor.fetchall()
finally:
    connection.close()  # Returns to pool

Monitor Connections

-- Current connections
SHOW PROCESSLIST;

-- Or detailed view
SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    info
FROM information_schema.PROCESSLIST
ORDER BY time DESC;

-- Kill long-running query
KILL 12345;

-- Connection statistics
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Max_used_connections';

-- Check if max_connections is sufficient
-- Max_used_connections should be < max_connections

Monitoring and Profiling

Performance Schema

-- Enable performance schema (my.cnf)
performance_schema = ON

-- Top queries by execution time
SELECT
    DIGEST_TEXT as query,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000 as avg_ms,
    SUM_TIMER_WAIT/1000000000 as total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Table I/O statistics
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    COUNT_READ,
    COUNT_WRITE,
    COUNT_FETCH,
    COUNT_INSERT,
    COUNT_UPDATE,
    COUNT_DELETE
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema')
ORDER BY COUNT_READ + COUNT_WRITE DESC
LIMIT 10;

Slow Query Analysis

# Analyze slow query log with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Options:
# -s: sort (t=time, c=count, l=lock time)
# -t: top N queries
# -a: don't abstract numbers

# More detailed analysis with pt-query-digest (Percona Toolkit)
pt-query-digest /var/log/mysql/slow-query.log

Key Metrics to Monitor

-- Connections
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Query throughput
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';

-- InnoDB metrics
SHOW STATUS LIKE 'Innodb_rows_read';
SHOW STATUS LIKE 'Innodb_rows_inserted';
SHOW STATUS LIKE 'Innodb_rows_updated';
SHOW STATUS LIKE 'Innodb_rows_deleted';

-- Buffer pool efficiency
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- Temporary tables
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- Table locks
SHOW GLOBAL STATUS LIKE 'Table_locks_waited';
SHOW GLOBAL STATUS LIKE 'Table_locks_immediate';

MySQL Tuner

# Download and run MySQL Tuner script
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl

# Provides recommendations for:
# - Memory usage
# - Query cache
# - Slow queries
# - Binary logs
# - Thread cache
# - Table cache
# - InnoDB settings

Performance Tuning Checklist

• Configure innodb_buffer_pool_size (70-80% of RAM)
• Enable innodb_file_per_table
• Set innodb_flush_log_at_trx_commit appropriately
• Adjust innodb_io_capacity for your storage
• Configure max_connections based on workload
• Enable slow query log and analyze regularly
• Create indexes on frequently queried columns
• Remove unused and redundant indexes
• Use EXPLAIN to analyze query plans
• Optimize queries to avoid table scans
• Implement connection pooling
• Monitor buffer pool hit ratio (target > 99%)
• Set up application-level caching
• Regular OPTIMIZE TABLE for fragmented tables
• Monitor temporary tables created on disk
• Partition large tables
• Archive historical data
• Use appropriate data types

Related Resources

• PostgreSQL vs MySQL
• Database Monitoring Best Practices
• Common MySQL Errors

Optimize your MySQL performance with Pulse. Get real-time monitoring, query analysis, and actionable insights to keep your database running at peak performance.