January 27, 2026Guides

How to Optimize MySQL Performance

Complete guide to optimizing MySQL database performance: configuration tuning, query optimization, indexing, and monitoring.

MySQL performance optimization is crucial for applications handling large amounts of data or high traffic. This guide covers essential techniques to improve MySQL performance, from configuration tuning to query optimization.

Optimizing MySQL Configuration

Edit MySQL configuration file to optimize performance based on your server resources:

bash

# Edit MySQL configuration (Ubuntu/Debian)
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Edit MySQL configuration (CentOS/RHEL)
sudo nano /etc/my.cnf

# Add or modify these settings:
[mysqld]
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
max_connections = 200
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
max_heap_table_size = 64M

# Restart MySQL
sudo systemctl restart mysql  # Ubuntu/Debian
sudo systemctl restart mysqld   # CentOS/RHEL

InnoDB Optimization

InnoDB is MySQL's default storage engine. Optimize it for better performance:

bash

# In /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf
[mysqld]
# InnoDB buffer pool (use 70-80% of available RAM)
innodb_buffer_pool_size = 2G

# InnoDB log file size
innodb_log_file_size = 512M

# InnoDB flush method (for SSD)
innodb_flush_method = O_DIRECT

# InnoDB I/O threads
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# InnoDB flush log at transaction commit
innodb_flush_log_at_trx_commit = 2

Query Cache Configuration

Query cache can improve performance for read-heavy workloads (Note: Query cache is deprecated in MySQL 8.0):

bash

# For MySQL 5.7 and earlier
[mysqld]
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Check query cache status
mysql> SHOW VARIABLES LIKE 'query_cache%';

# Check query cache statistics
mysql> SHOW STATUS LIKE 'Qcache%';

Index Optimization

Proper indexing is crucial for query performance:

bash

# Analyze table to update index statistics
ANALYZE TABLE table_name;

# Check unused indexes
SELECT * FROM sys.schema_unused_indexes;

# Create index
CREATE INDEX idx_column ON table_name(column_name);

# Create composite index
CREATE INDEX idx_multi ON table_name(col1, col2, col3);

# Show indexes
SHOW INDEXES FROM table_name;

# Drop unused index
DROP INDEX idx_name ON table_name;

Query Optimization

Optimize your SQL queries for better performance:

Use EXPLAIN to analyze query execution plans: EXPLAIN SELECT * FROM table WHERE column = 'value'
Avoid SELECT * - only select needed columns
Use LIMIT to restrict result sets
Use proper WHERE clauses with indexed columns
Avoid functions in WHERE clauses (e.g., WHERE YEAR(date) = 2026 → WHERE date >= '2026-01-01')
Use JOINs instead of subqueries when possible
Use UNION instead of OR for multiple conditions when appropriate

Monitoring MySQL Performance

bash

# Check MySQL status
mysql> SHOW STATUS;

# Check slow queries
mysql> SHOW VARIABLES LIKE 'slow_query%';

# Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# Check process list
mysql> SHOW PROCESSLIST;

# Check table status
mysql> SHOW TABLE STATUS LIKE 'table_name';

# Check InnoDB status
mysql> SHOW ENGINE INNODB STATUS;

Performance Optimization Tips

Regularly run OPTIMIZE TABLE to defragment tables
Monitor slow query log and optimize slow queries
Use connection pooling to reduce connection overhead
Partition large tables by date or range for better performance
Use read replicas for read-heavy workloads
Keep MySQL updated to the latest stable version
Monitor disk I/O and consider using SSD/NVMe storage
Use EXPLAIN ANALYZE (MySQL 8.0+) for detailed query analysis