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!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

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.