How to Profile and Optimize Slow MySQL Queries

Slow queries are the most common cause of website performance issues.

Enable Slow Query Log

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

slow_query_log = 1\nslow_query_log_file = /var/log/mysql/slow.log\nlong_query_time = 1

systemctl restart mysql

Analyze Slow Queries

mysqldumpslow /var/log/mysql/slow.log

Use EXPLAIN

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Common Fixes

• Add indexes: ALTER TABLE users ADD INDEX idx_email (email);
• Avoid SELECT *: Only query needed columns
• Use LIMIT: Don't fetch more rows than needed
• Optimize JOINs: Ensure joined columns are indexed