Reviewing the MySQL Slow Query Log Weekly Before Slow Pages Turn Into Tickets

Most slow database queries get noticed the same way: a customer says the checkout page hangs, or the dashboard times out, and only then does someone open the database server to look. By that point the bad query has usually been running for days. MySQL and MariaDB both have a built-in way to catch these earlier, the slow query log, but it only helps if it is configured with sensible thresholds and someone actually reads it on a schedule. This article walks through how the server decides whether a statement belongs in that log, why some slow pages never show up in it, and a simple weekly routine for reading it.

How mysqld decides a statement is slow

The slow query log is not a profiler running alongside every query. It is a filter that the server applies at the end of each statement. When a statement finishes and its locks are released, the server compares what it just measured against a few rules, and if the statement passes, one entry is written. The main rules are:

slow_query_log must be on. With it off, nothing else matters.
The statement's execution time must be greater than long_query_time. This value is in seconds and accepts fractions, so 0.5 means half a second. The default is 10 seconds, which is far too high for a web application where a page typically runs dozens of queries.
The statement must have examined at least min_examined_row_limit rows. The default is 0, so this rule usually does nothing unless you raise it.
Administrative statements like ALTER TABLE or OPTIMIZE TABLE are only logged if log_slow_admin_statements is enabled.

There is one exception to the time rule. If log_queries_not_using_indexes is on, a statement that did a full table scan or full index scan gets logged even if it was fast. More on that below. Two details of the mechanism explain a lot of confusion. First, in MySQL the time spent waiting for the initial table locks is not counted as execution time. It is reported separately as Lock_time. A query that sat blocked behind another transaction may look fast in Query_time but show a large Lock_time, so read both columns. Second, because the entry is written after the statement completes, entries appear in completion order, not start order. A long query that started first may show up after many shorter ones.

Turning it on without restarting anything

On a VPS or dedicated server you control, you can enable the log at runtime and persist it in the config file so it survives a restart. Check the current state first with SHOW GLOBAL VARIABLES LIKE 'slow_query%'; and SHOW GLOBAL VARIABLES LIKE 'long_query_time';, which are read-only. A reasonable starting point in the server config (often under /etc/mysql/ or /etc/my.cnf.d/, depending on distro and panel):

INI

[mysqld]
slow_query_log = 1
# Path is illustrative; pick one the mysql user can write to
slow_query_log_file = /var/lib/mysql/slow.log
# Seconds, fractions allowed. 1 is a common starting point for web apps
long_query_time = 1
# Leave off at first; it is noisy on small tables
log_queries_not_using_indexes = 0

To apply it without a restart, the equivalent SET GLOBAL statements work for these variables, but there is a catch that trips people up. long_query_time has both a global and a session value. When a client connects, its session copies the global value at that moment. Changing the global value later does not affect connections that are already open. On a busy site with persistent connections or a long-lived worker pool, you can lower the threshold and see nothing new in the log until those connections cycle. That is expected behavior, not a broken config. On shared hosting, changing global variables needs a privilege such as SUPER or, in MySQL 8, SYSTEM_VARIABLES_ADMIN, which regular accounts do not have. In that case the practical step is to ask the host whether slow logging can be enabled or whether they can share entries for your database. This is one of the more common requests at a support desk, and it goes faster when the customer already knows which page is slow and roughly when. MariaDB follows the same model. Newer releases also accept alternative names such as log_slow_query and log_slow_query_time, with the older names kept as aliases, so check which names your version documents before copying configs between servers.

What one entry tells you

Each entry starts with a header block, then the statement itself. Field order and extra fields vary between MySQL and MariaDB versions.

TEXT

# Illustrative output format
# Time: 2026-10-20T08:15:02.123456Z
# User@Host: shopuser[shopuser] @ localhost []
# Query_time: 3.412  Lock_time: 0.000  Rows_sent: 20  Rows_examined: 480000
SET timestamp=1760948102;
SELECT * FROM wp_posts WHERE post_status = 'publish' ORDER BY post_date DESC LIMIT 20;

The most useful signal here is the gap between Rows_examined and Rows_sent. In this illustrative example, the server read close to half a million rows to return twenty. That pattern almost always means the query cannot use an index for its filter or sort, so the engine reads a large part of the table and throws most of it away. A query with similar numbers on both sides that is still slow is a different problem: it is returning too much data, or the server is short on memory or disk I/O. A large Lock_time with a small Query_time points away from the query and toward whatever was holding the lock, often a long write, a backup, or a schema change running at the same time.

What the slow log will not show you

The time rule is per statement, which means a slow page made of many fast queries never appears. WordPress is a good example. On every request it loads all autoloaded options from wp_options in one query. That query may finish well under any threshold you set, yet when the autoloaded data grows into megabytes it is pulled and parsed on every click, and the dashboard feels heavy. If the slow log is clean but wp-admin still crawls, check autoload size directly; the steps are covered in Auditing Autoloaded Options to Stop Recurring WordPress Out-of-Memory Errors. The same applies to N+1 patterns, where a plugin runs one small query per item in a loop. Each query is fast, the page is not. For those, statement digests in performance_schema or application-level profiling are better tools. This is also where log_queries_not_using_indexes fits, with care. It catches queries that scan a table even when they are fast today. That is useful on a growing table, because a scan that takes a few milliseconds now will get slower as rows accumulate. On a typical CMS database, though, many small lookup tables are scanned legitimately, and the log fills with noise. If you turn it on, do it for a limited window, raise min_examined_row_limit so tiny tables are ignored, and on MySQL consider log_throttle_queries_not_using_indexes to cap how many such entries are written per minute.

A weekly routine that stays small

Reading the raw file is tedious because the same query appears hundreds of times with different values. mysqldumpslow, which ships with MySQL and is commonly available on MariaDB installs too, groups entries by pattern by replacing numbers with N and strings with 'S', then sorts the groups. The routine that tends to hold up is: rotate the log, then analyze the file you just closed. Rotating first gives you a clean one-week window and keeps the file from growing without limit.

BASH

# 1. Move last week's log aside (path is illustrative)
mv /var/lib/mysql/slow.log /var/lib/mysql/slow.log.lastweek

# 2. Tell the server to close the old file handle and open a new slow.log
mysql -e 'FLUSH SLOW LOGS;'

# 3. Top 10 patterns by total time, then by how often they ran
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log.lastweek
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log.lastweek

The mv alone is not enough. The server keeps writing to the open file descriptor, which still points at the renamed file, until it is told to reopen. FLUSH SLOW LOGS does that. If your distro or panel already ships a logrotate rule for MySQL logs, check whether it covers the slow log before adding your own, so the two do not fight. When reading the results, sorting by total time (-s t) shows where the server spent most of its week, which is usually where an index or a query rewrite pays off most. Sorting by count (-s c) shows what runs constantly. A pattern that is new this week compared to last week is the most valuable find of all: it usually lines up with a plugin update, a theme change, or a new report someone added, and catching it now is the difference between a quiet fix and a ticket. For each pattern worth acting on, take one real example from the raw file and run EXPLAIN on it to see whether an index is used. Keep a short note of what you found each week. After a few weeks that note becomes a baseline, and anything unusual stands out quickly. The practical version is this: set long_query_time to something a web page can actually tolerate, around one second as a starting point, rotate the log weekly, and spend a few minutes with mysqldumpslow comparing this week to last. Remember that the log only sees individual slow statements, so when a page is slow and the log is quiet, look at frequent small queries and autoloaded data instead.