MySQL Slow Query Log Analysis for Production Tuning

The MySQL slow query log records statements that run longer than a threshold, and analyzing it shows which queries cost the most in total time, not just which single execution was slowest. Enable it with a sensible long_query_time, group queries by their normalized form, and tackle the ones that consume the most cumulative time. Use EXPLAIN to understand each one before changing indexes or SQL.

How Do You Enable the Slow Query Log Safely?

The slow query log is controlled by server variables that you can often change without a restart. The key settings are whether logging is on, where it writes, and the time threshold.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = 'OFF';
SHOW VARIABLES LIKE 'slow_query_log_file';

Choose the threshold based on your goals. A value of one second catches only badly slow statements. A lower value, such as a few hundred milliseconds, finds more candidates but increases the volume. For a short investigation, you can set it to zero to capture everything, but be careful on busy servers because the log can grow very fast and the writing itself costs performance.

Be cautious with the option that logs queries not using indexes. It can flood the log with harmless fast queries on small tables. Persist settings in the configuration file so they survive restarts, and confirm variable names and defaults in the MySQL documentation for your version.

What Does the Log Contain?

Each entry records the execution time, lock time, the number of rows sent and examined, the user and host, a timestamp, and the SQL text. The ratio between rows examined and rows sent is especially telling.

• Query time: total execution time for that run.
• Lock time: time spent waiting for locks, which points to contention instead of inefficient plans.
• Rows sent: how many rows were returned to the client.
• Rows examined: how many rows the engine read. A large number compared with rows sent suggests a missing or unusable index.

A query that examines a million rows to return ten is a prime candidate for an index. A query with high lock time needs a look at transactions and concurrent writers, not the query text.

How Do You Summarize and Prioritize?

Reading the raw log is unproductive at scale. The same query shape appears thousands of times with different literal values. Use a tool that normalizes statements, replacing literals with placeholders, and aggregates statistics per query shape. MySQL ships a summarizing utility, and several third-party tools exist that do the same.

Sort by total time, which is the count of executions multiplied by the average time. A query that takes 50 milliseconds but runs a million times a day costs far more than a report that takes ten seconds once. Optimizing the high-total queries gives the biggest benefit.

• Look at the top few query shapes by total time, then by count and by average time.
• Note which tables and clauses repeat across the top entries. A single missing index often explains several.
• Compare logs from before and after changes to confirm improvement.
• Consider the Performance Schema as a complementary source, which tracks statement statistics without a log file. Check availability for your version.

How Do You Fix What You Find?

Run EXPLAIN, or EXPLAIN ANALYZE where supported, on the worst offenders using realistic parameters. Look at the access type, the index chosen, the rows estimated, and any filesort or temporary table.

• Add or adjust indexes to match the filter and sort columns, thinking about column order in composite indexes.
• Rewrite queries that fetch more rows or columns than needed, and avoid functions on indexed columns in conditions.
• Fix N+1 patterns in the application, where many small queries replace one efficient query.
• Reduce lock contention by shortening transactions and accessing rows in a consistent order.
• Cache results that rarely change, when it does not compromise correctness.

Test changes in staging with production-like data volumes, since plans change with data size. Add indexes carefully, because each one slows writes and takes space.

Frequently Asked Questions

Does the slow query log hurt performance?

With a reasonable threshold the overhead is small. Logging everything on a busy server can be significant, so do it briefly.

What threshold should I use for long_query_time?

Start around one second, then lower it as you fix the worst queries, based on your latency goals.

Why is a fast query appearing in the log?

It may be logged because of the not-using-indexes option, or because it was slow once due to lock waits or cold caches.

Should I sort by average or total time?

Start with total time, since it reflects overall cost, and check the average to understand each query.

Summary

Enable the slow query log with a sensible threshold, summarize by query shape, prioritize by total time, and verify fixes with EXPLAIN. Check the MySQL documentation for variable behavior in your version.