How to Find Slow Queries with the MySQL Slow Query Log

When a MySQL database responds slowly, the SQL statements taking the most time are a useful place to investigate. The slow query log records statements that exceed a time threshold and meet a row-examination threshold. A database administrator can use those records to find expensive queries, see how long they ran, and decide which ones to examine more closely.

The log is disabled by default. To use it, enable logging, choose a threshold that fits your workload, and inspect the resulting entries. MySQL’s slow query log documentation describes the settings and output format.

Enable the log and set a threshold

MySQL logs a statement when it runs longer than the time set by long_query_time and checks at least the number of rows set by min_examined_row_limit. The default time threshold is 10 seconds. MySQL accepts fractional values, so you can set a threshold with microsecond resolution. A lower threshold captures more statements and produces a larger log.

Enable the log at runtime and choose a threshold. The value below is an example; select a threshold that makes sense for the queries you need to investigate.

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;

Check the active settings and find the log file’s location with:

SHOW GLOBAL VARIABLES
WHERE Variable_name IN (
  'slow_query_log',
  'long_query_time',
  'min_examined_row_limit',
  'log_output',
  'slow_query_log_file'
);

A global change to long_query_time applies to new connections. If you are testing from a connection that was already open, set the threshold for that session too:

SET SESSION long_query_time = 2;

Runtime changes do not survive a server restart. To keep the settings, add them under the [mysqld] section of the MySQL configuration file, commonly named my.cnf or mysqld.cnf:

[mysqld]
slow_query_log=ON
long_query_time=2

Many configurations make MySQL write to a file by default. The log_output setting controls whether MySQL writes entries to a file, a database table, or both. Check the active value and the path returned by slow_query_log_file. If MySQL cannot write to the selected file, the log may stop receiving entries; confirm that the MySQL server account has permission to write there. Logging to a table uses more server resources than logging to a file, as the MySQL slow-log overview notes.

Read the fields to find costly queries

A slow-log entry includes the time, the user and host that issued the statement, Query_time, Lock_time, Rows_sent, Rows_examined, and the SQL text. These fields help distinguish a statement that scans many rows from one that returns little data after examining much more.

Rows_examined counts rows MySQL checked while processing the statement; Rows_sent counts rows it returned. A large gap between them is a reason to inspect how the statement filters and joins data. Query_time reports how long the query ran, while Lock_time reports how long MySQL spent waiting to acquire locks. The log entry is written after the statement finishes and its locks are released, so its appearance time does not mark the moment the query began.

For example, if an entry shows a high Rows_examined count and a low Rows_sent count, review the statement’s filters and use EXPLAIN to inspect how MySQL plans to read the tables. The slow log identifies candidates for investigation; the entry alone does not prove which change will improve performance.

Group repeated statements before tuning

A log file can contain many entries, and reading each one by hand takes time. MySQL’s mysqldumpslow utility summarizes similar statements and sorts them by a chosen measure. To display the 10 statements with the highest total time, run:

mysqldumpslow -s t -t 10 /path/to/slow.log

For more detail, Percona Toolkit’s pt-query-digest groups statements by fingerprint, a standardized form of a query that helps identify repeated patterns. It reports statistics for each group. The Percona analysis tool helps you compare a query pattern’s total time, frequency, and other measures instead of inspecting individual executions.

Prioritize patterns that run often and account for substantial total time. Then inspect representative SQL and its execution plan. A single unusually slow statement and a frequently repeated statement affect database performance in different ways, so compare the time each call takes with the time all calls add up to.

Check why a query is missing

An empty or incomplete log can coexist with slow statements. First, verify that slow_query_log is enabled, that log_output points to the destination you are checking, and that MySQL can write to the selected file. Then check long_query_time and min_examined_row_limit. Under the normal logging rules, MySQL skips a statement if it finishes below the time threshold or checks fewer rows than the row limit.

For example, SELECT SLEEP(3) waits for three seconds but examines no table rows. A nonzero min_examined_row_limit can therefore keep it out of the slow query log even when it runs longer than the time threshold. The row limit and other causes of missing entries are discussed in this slow-log troubleshooting thread.

MySQL also excludes administrative statements such as ALTER TABLE and CREATE INDEX by default. Set log_slow_admin_statements to include them. If you enable log_queries_not_using_indexes, MySQL logs statements that do not use indexes even when they run quickly; this option can make the log grow rapidly. MySQL provides log_throttle_queries_not_using_indexes to limit how many such statements it logs in each 60-second window.

You can also check the cumulative count of slow queries since the server started:

SHOW GLOBAL STATUS LIKE 'Slow_queries';

This count tracks queries that exceeded long_query_time, even when the slow query log is disabled. It does not confirm that MySQL wrote log entries. Use the log itself or a summary tool to find the statements.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com