MySQL Performance Tuning: Speed up database queries

Database performance directly impacts website speed. Slow queries cause sluggish pages, high CPU usage, and can crash your server under load. Performance tuning involves finding bottlenecks and fixing them strategically.

Identify slow queries

Enable MySQL slow query log:

# In /etc/my.cnf or /etc/mysql/my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2  # Log queries taking >2 seconds

View slow queries:

tail -50 /var/log/mysql/slow-query.log

Analyze with mysqldumpslow:

mysqldumpslow /var/log/mysql/slow-query.log | head -20

Add indexes strategically

Indexes speed up WHERE clauses and JOINs dramatically:

-- Good: Index on columns frequently used in WHERE
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_post_date ON posts(published_date);

-- Better: Multi-column index for common queries
CREATE INDEX idx_user_status_date ON users(status, created_date);

Warning: Too many indexes slow down writes. Index strategically:

• Columns in WHERE clauses
• Columns in JOIN conditions
• Columns in ORDER BY

Use EXPLAIN to analyze

EXPLAIN shows how MySQL executes a query:

EXPLAIN SELECT * FROM posts WHERE user_id = 5;

Key columns to watch:

• type: How MySQL accesses data
  • ALL = full table scan (bad)
  • range = uses index range (good)
  • ref = uses index lookup (great)
• key: Which index is being used
• rows: How many rows examined (lower is better)
• Extra: Using filesort, temporary table = slow

MySQL configuration tuning

Caution: Config changes require MySQL restart. Test on non-production first.

Query optimization tips

• SELECT * is wasteful: Select only needed columns

  -- Bad
  SELECT * FROM users WHERE id = 5;
  
  -- Good
  SELECT id, name, email FROM users WHERE id = 5;

• Avoid subqueries in SELECT: Use JOIN instead

  -- Slower
  SELECT name, (SELECT COUNT(*) FROM posts WHERE user_id=users.id) as posts FROM users;
  
  -- Faster
  SELECT users.name, COUNT(posts.id) as posts FROM users LEFT JOIN posts ON posts.user_id=users.id GROUP BY users.id;

• Use LIMIT to reduce data: Don't fetch unnecessary rows
• Avoid LIKE with leading %: Can't use index efficiently

Monitor performance

Check current queries:

SHOW PROCESSLIST;

Monitor via cPanel/aaPanel/CloudPanel:

• Most panels show database connections and resource usage
• Watch for high CPU, memory, disk I/O
• Check for long-running queries

WordPress-specific optimization:

• Use WP-Optimize or similar caching plugin
• Enable WordPress query caching
• Clean up old posts/comments/spam

Related: Database optimization | Database indexes | Slow query analysis

Related articles