MySQL Running Slow? 10 Optimization Tips (2025)

Is your MySQL database crawling at a snail's pace? Slow database queries can cripple your application performance, frustrate users, and waste server resources.

In this guide, I'll show you 10 proven MySQL optimization techniques that can dramatically improve your database performance - from quick wins to advanced tuning.

1. Identify Slow Queries First

Before optimizing, you need to know which queries are slow. Enable the slow query log:

# Edit MySQL configuration
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add these lines:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2  # Log queries taking > 2 seconds
log_queries_not_using_indexes = 1

# Restart MySQL
sudo systemctl restart mysql

Analyze slow queries:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

2. Add Missing Indexes

Indexes are the #1 way to speed up queries. If you're filtering, sorting, or joining on a column, it needs an index.

Find missing indexes:

# Check columns used in WHERE clauses without indexes
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

# If "type" is "ALL" (full table scan), you need an index!

Add indexes:

# Single column index
CREATE INDEX idx_customer_id ON orders(customer_id);

# Composite index (for multiple columns in WHERE)
CREATE INDEX idx_user_date ON orders(user_id, created_at);

# Check existing indexes
SHOW INDEX FROM orders;

3. Optimize Your Queries

❌ Bad practices:

# Don't use SELECT *
SELECT * FROM users WHERE id = 1;

# Don't use wildcard at the beginning
SELECT * FROM users WHERE email LIKE '%gmail.com';

# Don't use functions on indexed columns
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

✅ Good practices:

# Select only needed columns
SELECT id, name, email FROM users WHERE id = 1;

# Use wildcard at the end
SELECT * FROM users WHERE email LIKE 'john%';

# Compare directly
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

4. Tune InnoDB Buffer Pool

The buffer pool is MySQL's cache for data and indexes. On a dedicated database server, set it to 70-80% of available RAM. If you experience memory issues, check our out of memory error troubleshooting guide.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

# Add under [mysqld]:
innodb_buffer_pool_size = 2G  # Adjust based on your RAM
innodb_buffer_pool_instances = 2  # 1 instance per GB

sudo systemctl restart mysql

Check buffer pool usage:

SHOW STATUS LIKE 'Innodb_buffer_pool%';

5. Increase Connection Limits

If you see "Too many connections" errors:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

max_connections = 500  # Default is often 150
wait_timeout = 300
interactive_timeout = 300

sudo systemctl restart mysql

Check current connections:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

6. Use Connection Pooling

Opening a new database connection for every request is slow. Use connection pooling in your application.

Node.js example:

const pool = mysql.createPool({
  host: 'localhost',
  user: 'dbuser',
  password: 'dbpass',
  database: 'mydb',
  connectionLimit: 10  // Reuse connections
});

Python example:

from sqlalchemy import create_engine

engine = create_engine(
    'mysql+pymysql://user:pass@localhost/db',
    pool_size=10,
    max_overflow=20
)

7. Optimize Table Structure

Use appropriate data types:

# ❌ Bad - wastes space
VARCHAR(255) for country codes  # Only need 2 chars

# ✅ Good
CHAR(2) for country codes
TINYINT for small numbers (0-255)
ENUM for fixed choices
INT UNSIGNED for IDs (no negative numbers)

Normalize large TEXT/BLOB columns:

If you have large text fields, move them to separate tables:

# Instead of:
CREATE TABLE articles (
  id INT,
  title VARCHAR(200),
  content TEXT  # Large field
);

# Do this:
CREATE TABLE articles (
  id INT,
  title VARCHAR(200)
);

CREATE TABLE article_content (
  article_id INT,
  content TEXT
);

8. Enable Query Cache (MySQL 5.7 and earlier)

Note: Query cache is removed in MySQL 8.0+. If you're on 5.7 or earlier:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

sudo systemctl restart mysql

For MySQL 8.0+, use Redis or application-level caching instead.

9. Optimize with MyISAM (Read-Heavy Tables Only)

If you have tables that are read 99% of the time (no updates), MyISAM can be faster than InnoDB:

ALTER TABLE read_only_logs ENGINE = MyISAM;

10. Regular Maintenance

Analyze and optimize tables:

# Analyze table (updates index statistics)
ANALYZE TABLE orders;

# Optimize table (defragments and reclaims space)
OPTIMIZE TABLE orders;

# Check for corruption
CHECK TABLE orders;

Automate with a cron job:

sudo crontab -e

# Run every Sunday at 3 AM
0 3 * * 0 mysqlcheck -u root -p'password' --auto-repair --optimize --all-databases

Bonus: Monitor MySQL Performance

Regular monitoring is essential for maintaining optimal performance. Learn more about VPS performance monitoring and analyzing log files.

Key metrics to watch:

# Overall status
SHOW GLOBAL STATUS;

# Key performance indicators
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Slow_queries';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';

# Active queries
SHOW PROCESSLIST;

Install performance monitoring tools:

• MySQLTuner: wget mysqltuner.pl && perl mysqltuner.pl
• Percona Monitoring: Free MySQL monitoring
• phpMyAdmin: Web-based administration

Quick Performance Checklist

1. ✅ Enable slow query log and analyze queries
2. ✅ Add indexes on columns used in WHERE, ORDER BY, JOIN
3. ✅ Select only needed columns (avoid SELECT *)
4. ✅ Tune innodb_buffer_pool_size (70% of RAM for dedicated servers)
5. ✅ Increase max_connections if needed
6. ✅ Use connection pooling in your application
7. ✅ Choose appropriate data types
8. ✅ Run ANALYZE TABLE and OPTIMIZE TABLE regularly
9. ✅ Monitor key performance metrics

Conclusion

MySQL performance optimization is an ongoing process. Start with these 10 techniques:

1. Identify slow queries with the slow query log
2. Add missing indexes
3. Optimize query patterns
4. Tune the InnoDB buffer pool
5. Configure connection limits appropriately
6. Implement connection pooling
7. Choose optimal data types
8. Enable query cache (MySQL 5.7) or application cache
9. Consider MyISAM for read-only tables
10. Perform regular maintenance

Remember: measure first, optimize second. Always use EXPLAIN and the slow query log to identify bottlenecks before making changes.