MySQL slow query log — 6 steps to identify and fix performance issues

Source: Dev.to

Here’s a cleaned‑up version of the markdown snippet with proper formatting:

**Source:** [Dev.to – MySQL Slow Query Log: 6 Steps to Identify and Fix Performance Issues](https://dev.to/finny_collins/mysql-slow-query-log-6-steps-to-identify-and-fix-performance-issues-31ei)

MySQL Slow Query Log – A Practical Guide

MySQL’s slow query log is one of the most practical tools you have for tracking down performance problems. It records queries that take longer than a threshold you define, so instead of guessing where your database is slow, you get a list of actual offenders.

This guide walks through enabling the log, reading it, and acting on what you find. Six concrete steps, no hand‑waving.

What the slow query log actually does

The slow query log captures queries that exceed a configured execution‑time threshold. It writes them to a file (or a table) along with:

• execution time
• rows examined
• rows sent
• timestamp

Key points

• It does not run continuously in the background eating CPU. The overhead is minimal—mostly the cost of writing to disk when a slow query is detected.
• Most production databases run with it enabled permanently.

Two settings control the most important behavior

Setting long_query_time to 0 logs everything, which is useful for short‑term profiling but not something you’d leave on in production.

Step 1: Enable the Slow Query Log

You can enable it without restarting MySQL by running these commands:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

• This takes effect immediately for new connections.
• Existing connections keep the old settings until they reconnect.

Make it persistent

Add the following to your my.cnf (Linux) or my.ini (Windows):

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

log_queries_not_using_indexes catches queries that skip indexes entirely, even if they finish quickly on small tables—those same queries will become a problem as the table grows.

After editing the config file, restart MySQL for the settings to take effect.

Step 2: Set the Right Threshold

The default long_query_time is 10 seconds, which is too high for most applications. A query that takes 10 seconds is already causing visible problems.

• Web applications – start with 1 second.
• APIs / dashboards where users expect sub‑second responses – drop to 0.5 s or even 0.2 s.

There is no single right answer — it depends on what your application is doing. A reporting query joining multiple large tables might reasonably take 3–5 seconds. A login check taking 800 ms is a problem.

If you are debugging a specific issue and want to catch everything, temporarily set it to 0:

SET GLOBAL long_query_time = 0;

Run your test, examine the log, then set it back to a reasonable value.

Step 3: Read the log with mysqldumpslow

Raw slow‑query log files are hard to scan manually. mysqldumpslow ships with MySQL and summarizes the log by grouping similar queries together.

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

• -s t – sort by total time.
• -t 10 – show the top 10 entries.

This gives you the queries that are costing the most time overall, not just the ones that were slowest on a single execution.

Other useful sort options

Look at rows examined versus rows sent. A query that examines 500 000 rows to return 10 is doing a lot of unnecessary work and is a strong signal that an index is missing or being bypassed.

Third‑party alternative

pt-query-digest from Percona Toolkit produces more detailed output than mysqldumpslow and handles edge cases better. If you spend a lot of time analyzing slow‑query logs, it’s worth installing.

Step 4 – Run EXPLAIN on the Slow Queries

After you have identified the problematic queries, the next step is to understand why they are slow.
EXPLAIN shows how MySQL plans to execute a query.

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42
  AND status = 'pending';

The output is the execution plan – it tells you which tables are accessed, in what order, which indexes are used (or not used), and how many rows MySQL expects to examine.

Columns to Watch

Tip: In MySQL 8.0+ you can use EXPLAIN ANALYZE. It actually runs the query and reports real execution times, which is far more reliable when the optimizer’s estimates are off.

Note: A full table scan on a small table may be acceptable, but on a table with millions of rows it is almost always a performance problem.

Step 5 – Add or Fix Indexes

Most slow queries stem from missing indexes. Once you know which column(s) a query filters or joins on, adding an appropriate index is often all it takes.

CREATE INDEX idx_orders_customer_status
    ON orders (customer_id, status);

• Consider composite indexes when a query filters on multiple columns.
• The order of columns in the index should match the order used in the WHERE clause for optimal utilization.

Things to Keep in Mind

• Column order in composite indexes matters. Put the most selective column first, and match the order to how the query filters.
• Indexes speed up reads but slow down writes. A table that is written to constantly probably should not have ten indexes on it.
• Unused indexes waste space and add overhead. Check information_schema.INDEX_STATISTICS or use pt-index-usage to find indexes that are never used.

After adding an index, run EXPLAIN again and confirm MySQL is actually using it. Sometimes MySQL will still choose a full scan if its statistics are stale. Refresh them with:

ANALYZE TABLE orders;

Step 6 – Rewrite the Query When Indexing Is Not Enough

Indexes help MySQL find rows faster, but a poorly written query can remain slow even with good indexes.

Common patterns worth looking for

• SELECT * when you only need a few columns. Retrieving unnecessary columns increases I/O and network traffic.

• Functions applied to indexed columns in WHERE clauses.

  -- Bad (cannot use index on created_at)
  WHERE YEAR(created_at) = 2024

  Rewrite as:

  WHERE created_at >= '2024-01-01' 
    AND created_at < '2025-01-01'

• OR conditions that prevent index use. Splitting the query into two parts and combining with UNION ALL can be faster.

• Subqueries that run once per row instead of once total. These are often rewritable as a JOIN.

Rewriting queries is worth doing before reaching for schema changes. A query rewrite has no migration risk and takes effect immediately.

Keeping an Eye on Things Over Time

The slow‑query log tells you what is slow right now, but it doesn’t help you catch regressions before they become user‑visible problems. For that, you need continuous monitoring.

• Query performance tends to degrade gradually as tables grow.
  A query that was fine at 10 000 rows can start appearing in the slow‑query log at 1 000 000 rows.
• Checking the log regularly—or setting up alerts—helps you catch these issues before users start complaining.

Tip: If you’re running MySQL in production and care about your data, pair performance monitoring with a solid backup strategy.

A reliable MySQL backup solution is essential. Databasus is an industry‑standard tool for MySQL backups, suitable for both individual projects and larger teams.

Summary

The slow query log isn’t complicated. Enable it, set a sensible threshold, and you’ll instantly get a feed of actual slow queries from your production database.

From there, the process is straightforward:

1. Identify the worst offenders – use mysqldumpslow or pt-query-digest.
2. Analyze the execution plan – run EXPLAIN on the problematic queries.
3. Add missing indexes where needed.
4. Rewrite inefficient queries to improve logic and performance.

Most MySQL performance problems aren’t mysterious. They appear in the slow query log and have a clear cause—the hard part is finding the time to look at them.