How to Optimize MySQL Slow Queries

How to Optimize MySQL Slow Queries

Slow queries are one of the most common performance bottlenecks for applications running on Breeze instances. Identifying and optimizing these queries can dramatically improve response times, reduce CPU load, and lower memory consumption on your database server.

Enabling the Slow Query Log

The slow query log captures all queries that exceed a specified execution time. Enable it in your MySQL configuration at /etc/mysql/mysql.conf.d/mysqld.cnf:

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

Restart MySQL to apply:

sudo systemctl restart mysql

Setting long_query_time = 1 logs queries taking more than one second. Start here and lower the threshold as you optimize.

Analyzing Slow Queries with mysqldumpslow

MySQL includes a built-in tool to summarize the slow query log:

# Top 10 slowest queries by average time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# Top 10 queries by count (most frequent slow queries)
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

Using EXPLAIN to Understand Query Plans

Once you identify a slow query, use EXPLAIN to understand how MySQL executes it:

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01'
ORDER BY o.total DESC
LIMIT 50;

Key columns to examine in the EXPLAIN output:

• type — aim for ref, range, or const; avoid ALL (full table scan).
• key — shows which index MySQL chose; NULL means no index was used.
• rows — estimated rows MySQL must examine; lower is better.
• Extra — watch for Using filesort and Using temporary, which indicate expensive operations.

Adding Proper Indexes

The most impactful optimization is adding the right indexes:

-- Composite index for the query above
CREATE INDEX idx_orders_created_user ON orders (created_at, user_id, total);

-- Covering index (includes all columns needed by the query)
CREATE INDEX idx_orders_covering ON orders (user_id, created_at, total);

Guidelines for indexing:

• Index columns used in WHERE, JOIN, and ORDER BY clauses.
• Place high-cardinality columns first in composite indexes.
• Avoid over-indexing; each index adds write overhead and disk usage.

Query Rewriting Techniques

Common rewrites that improve performance:

-- BAD: Function on indexed column prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- GOOD: Range condition uses the index
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- BAD: SELECT * fetches unnecessary columns
SELECT * FROM users WHERE email = 'user@example.com';

-- GOOD: Select only needed columns
SELECT id, name, email FROM users WHERE email = 'user@example.com';

Server-Level Tuning

Adjust these key variables in your MySQL configuration to complement query optimization on your Breeze instance:

innodb_buffer_pool_size = 70% of available RAM
innodb_log_file_size = 256M
query_cache_type = 0  # Disabled in MySQL 8.0+
join_buffer_size = 4M
sort_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

After making changes, monitor the improvement using SHOW GLOBAL STATUS LIKE 'Slow_queries'; and verify your query response times have decreased.