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

Source: Dev.to

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, and timestamp.

• 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, look at the log, then set it back to something reasonable.

---

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

Once you have a list of problem queries, the next step is understanding 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 shows the execution plan — which tables are accessed, in what order, what indexes are used, and which are not.

Columns to watch

EXPLAIN ANALYZE (available in MySQL 8.0+) actually runs the query and shows real execution times, not just estimates. It is more useful when estimates are misleading.

A full table scan on a small table is not necessarily a problem. A full table scan on a table with millions of rows is.

---

Step 5: Add or fix indexes

Most slow queries come down to missing indexes. Once you know which column(s) a query is filtering or joining on, adding an 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 use.

---

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 10 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. Run:

ANALYZE TABLE orders;

to refresh them.

---

Step 6: Rewrite the Query When Indexing Is Not Enough

Sometimes the query itself is the problem. Indexes help MySQL find rows faster, but a poorly written query can still be 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 transfer.

• 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. Sometimes splitting into two queries with UNION ALL is faster.

• Subqueries that run once per row instead of once total. 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 does not help you catch regressions before they become user‑visible problems. For that, you need monitoring.

Query performance tends to degrade gradually as tables grow. A query that was fine at 10 000 rows can start showing up 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.

If you are running MySQL in production and care about the data, a solid MySQL backup setup is worth having alongside your performance monitoring. Databasus is an industry‑standard tool for MySQL backups, suitable for both individual projects and larger teams.

---

Summary

The slow query log is not a complicated tool. Enable it, set a sensible threshold, and you immediately have a feed of actual slow queries from your production database.

From there, the process is straightforward:

1. Use mysqldumpslow or pt-query-digest to find the worst offenders.
2. Run EXPLAIN to understand the execution plan.
3. Add indexes where they are missing.
4. Rewrite queries where the logic itself is inefficient.

Most performance problems in MySQL are not mysterious. They show up in the slow query log, and they have a clear cause. The hard part is making time to look.