How to Fix Slow MySQL Queries on Linux Server

Slow MySQL queries are the most common cause of slow WordPress and web application performance. This guide covers diagnosis and fixing.

Step 1 — Enable Slow Query Log

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

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

sudo systemctl restart mysql

Step 2 — Analyse Slow Query Log

# Parse slow query log
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# Real-time slow queries
sudo tail -f /var/log/mysql/slow.log

Step 3 — Use EXPLAIN on Slow Queries

EXPLAIN SELECT * FROM wp_posts WHERE post_status='publish' ORDER BY post_date DESC;
-- Look for: type=ALL (bad), key=NULL (no index used)
-- Want: type=ref or range, key=index_name

Step 4 — Add Missing Indexes

-- Add index on commonly queried column
ALTER TABLE wp_posts ADD INDEX idx_post_status_date (post_status, post_date);

-- Check existing indexes
SHOW INDEX FROM wp_posts;

-- WordPress specific
ALTER TABLE wp_options ADD INDEX autoload_idx (autoload);

Step 5 — Clean WordPress Database

-- Remove post revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Remove expired transients
DELETE FROM wp_options WHERE option_name LIKE '_transient_%' AND option_value < UNIX_TIMESTAMP();

-- Optimize all tables
wp db optimize

Step 6 — Tune MySQL Configuration

[mysqld]
innodb_buffer_pool_size = 1G  # 70% of available RAM
query_cache_size = 64M
query_cache_type = 1
tmp_table_size = 64M
max_heap_table_size = 64M

Conclusion

Slow queries need indexing and configuration tuning. Our team provides database performance optimisation for MySQL and MariaDB.