How to Analyze Slow Queries in MySQL

How do I enable, find, and fix slow queries in MySQL?

The slow query log captures SQL statements that exceed a time threshold, helping you detect and optimize poorly performing queries.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is the MySQL slow query log?

The slow query log records statements whose execution time exceeds long_query_time or that skip indexes when log_queries_not_using_indexes is enabled. Reading this file reveals which queries add latency to your application.

How do I enable the slow query log?

Activate logging at runtime with SET GLOBAL slow_query_log = ON; and define the file path, threshold, and other options. Use mysqladmin flush-logs to rotate logs without restarting.

Which settings matter most?

long_query_time (seconds) controls the cutoff. Lower it during troubleshooting (e.g., 0.5 s) and raise it once critical queries are fixed. Toggle log_queries_not_using_indexes to capture table scans.

How can I locate the worst offenders quickly?

Run mysqldumpslow -s t /var/log/mysql/slow.log to sort by execution time. For live sessions, query performance_schema.events_statements_summary_by_digest to aggregate metrics without reading files.

How do I diagnose a single slow query?

Copy the query into EXPLAIN ANALYZE (MySQL 8.0+). The plan highlights full scans, missing indexes, and row counts. Compare the plan before and after adding indexes or rewriting joins.

What index should I add for JOINs?

Focus on foreign keys and filter columns. Example: an index on OrderItems(order_id) speeds joins with Orders. Composite indexes often outperform multiple single-column indexes.

Best practices for sustained performance

Keep the slow query log enabled in staging. Regularly review performance_schema digests, archive old log files, and automate index suggestions with tools like pt-query-digest.

Common mistakes to avoid

Restarting MySQL to change log settings causes downtime—use SET GLOBAL instead. Forgetting to raise long_query_time after testing floods disks with trivial queries.

Why How to Analyze Slow Queries in MySQL is important

How to Analyze Slow Queries in MySQL Example Usage


-- Identify slow order-to-item join
EXPLAIN ANALYZE
SELECT o.id, SUM(oi.quantity * p.price) AS order_total
FROM Orders o
JOIN OrderItems oi  ON oi.order_id = o.id
JOIN Products   p   ON p.id = oi.product_id
WHERE o.order_date >= '2023-10-01'
GROUP BY o.id;

How to Analyze Slow Queries in MySQL Syntax


-- Enable logging
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;               -- seconds
SET GLOBAL log_queries_not_using_indexes = 1;

-- Review aggregated data (MySQL 8.0+)
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1e9 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Common Mistakes

Restarting MySQL after editing my.cnf. This causes avoidable downtime. Use SET GLOBAL to change slow-query settings live, then persist with SET PERSIST or an option file reload.
Leaving long_query_time at 0.5 s in production. This floods disks with harmless queries. After debugging, raise the value (e.g., 1-2 s) or disable log_queries_not_using_indexes.