Slow query analysis-find and fix performance issues

Slow queries hurt website performance. MySQL can log queries that take longer than a threshold (default 10 seconds) so you can identify and fix them.

Enable slow query log

Via VPS my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2  # log queries taking > 2 seconds

Then restart:

sudo systemctl restart mysql

Read the slow query log

sudo tail -100 /var/log/mysql/slow.log
# Shows queries taking > 2 seconds

Use EXPLAIN to analyze

EXPLAIN SELECT * FROM posts WHERE user_id = 5 AND status = 'published';
# Look for:
# - rows: high number = scanning too many rows (needs index)
# - key: NULL = no index used (add one)
# - type: ALL = full table scan (bad), should be const/ref (good)

Optimize slow queries

• Add index: Index columns in WHERE, JOIN, ORDER BY
• Use LIMIT: Don't SELECT 100k rows if you only need 10
• Remove JOINs: If possible, query one table at a time
• Archive old data: Move old posts/logs to separate table
• Use caching: Cache query results instead of running repeatedly

Related: Indexes | Performance tuning

Related articles