DEV Community

Cover image for MySQL Slow Query Log: Enable, Read and Find Slow Queries
devTalk
devTalk

Posted on Originally published at dev-talk.com

MySQL Slow Query Log: Enable, Read and Find Slow Queries

"The database is slow" is not a diagnosis. Before you add an index or rewrite a query, you need to know which statements are slow, how often they run, and how much work each one does. MySQL's slow query log records exactly that. This is Part 1 of a two-part series: here we turn the log on, read it, and group the results. Part 2 takes the worst offenders and fixes them with EXPLAIN and indexes.

Everything below was run on MySQL 8.0.46 (Ubuntu package) against a test schema with 500,000 rows in orders and 20,000 in users, both with only primary keys. The application-side examples ran on Laravel 13.40.0 and PHP 8.3. The numbers in the output blocks come from those runs.

Turn the slow query log on

The MySQL manual says the slow query log is disabled by default, that long_query_time defaults to 10 seconds, and that the file is named host_name-slow.log in the data directory unless you give it a path. Put the settings in your server config so they survive restarts:

[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time     = 1
Enter fullscreen mode Exit fullscreen mode

The directory must exist and be writable by the MySQL user. I used long_query_time = 0.03 in my tests only because my table is small and fast; start at one second in production and lower it as you fix the worst queries. After a restart with those settings:

mysql> SHOW VARIABLES WHERE Variable_name IN ('slow_query_log','long_query_time','log_queries_not_using_indexes','min_examined_row_limit','log_output');
log_output                      FILE
log_queries_not_using_indexes   OFF
long_query_time                 0.030000
min_examined_row_limit          0
slow_query_log                  ON
Enter fullscreen mode Exit fullscreen mode

Change it without a restart

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
Enter fullscreen mode Exit fullscreen mode

The manual documents slow_query_log and slow_query_log_file as runtime-changeable global variables. I toggled the log off and on and read the value back each time. A SET GLOBAL does not survive a restart, so put the permanent values in the config file.

The catch: existing connections keep the old threshold

A global change to long_query_time applies to new connections. I tested it: connection A was opened while the global value was 10 seconds, then the global value was lowered to 0.03. Both connections then ran the same slow query pattern:

entries logged: 1
logged: SELECT COUNT(*) FROM orders WHERE DATE(created_at) = "2026-03-16";
Enter fullscreen mode Exit fullscreen mode

Only the query from the new connection (…03-16) was logged. The old connection's query was not. That matters for PHP-FPM with persistent connections and for queue workers and Octane processes that stay up for days: after you lower the threshold, restart them or you will wonder why the log is empty.

What gets logged

Per the manual, a statement is written after it has executed and its locks have been released, so log order may differ from execution order. It must run longer than long_query_time and examine at least min_examined_row_limit rows. Time spent waiting for the initial locks is not counted as execution time. The manual's intro says "more than" the threshold while its decision list says "at least", so treat the exact boundary as unspecified.

I ran six statements: five slow ones plus a primary key lookup. Five were logged, and the lookup was not. Here is one raw entry with the volatile parts replaced:

# Time: <ts>
# User@Host: root[root] @ localhost []
# Query_time: <t>  Lock_time: <t> Rows_sent: 20  Rows_examined: 500020
use shop;
SET timestamp=<ts>;
SELECT * FROM orders WHERE user_id = 1234 ORDER BY created_at DESC LIMIT 20;
Enter fullscreen mode Exit fullscreen mode

The manual defines the fields: Query_time is the execution time in seconds, Lock_time the time to acquire locks, Rows_sent the rows returned to the client, and Rows_examined the rows examined by the server layer. The one to watch is the relationship between the last two. This query returned 20 rows after examining 500,020. All five entries:

Query Rows_sent Rows_examined
Latest 20 orders for one user 20 500,020
COUNT(*) with DATE(created_at) = ... 1 500,000
reference LIKE '%0042' 50 500,000
Pending orders in a 7-day window 2,016 500,000
Join of orders and users, grouped 5 600,005

Every one of them read the whole table to return a handful of rows. Execution time depends on caching and hardware, but a huge Rows_examined compared with Rows_sent tells you the query is missing an index or defeating one, and it is the same on every run.

Group the log with mysqldumpslow

A busy server logs the same query shape thousands of times with different literals. mysqldumpslow ships with MySQL and collapses them. I ran the same "latest orders for a user" query for five different user ids plus one other query:

mysqldumpslow -s c /var/log/mysql/mysql-slow.log
Enter fullscreen mode Exit fullscreen mode
Count: 5  Time=<t>s (<t>s)  Lock=<t>s (<t>s)  Rows=20.0 (100), root[root]@localhost
  SELECT * FROM orders WHERE user_id = N ORDER BY created_at DESC LIMIT N
Count: 1  Time=<t>s (<t>s)  Lock=<t>s (<t>s)  Rows=1.0 (1), root[root]@localhost
  SELECT COUNT(*) FROM orders WHERE DATE(created_at) = 'S'
Enter fullscreen mode Exit fullscreen mode

Numbers become N and strings become 'S', so the five calls count as one statement. -s picks the sort: c by count, t by total time. Sort by total time to find what costs the most overall, and by count to find what is called most. One quirk on my Ubuntu build: when -t (top N) was larger than the number of distinct statements, the script printed the groups and then ended with Died at /usr/bin/mysqldumpslow line 163. Leave -t off for small logs.

Percona's pt-query-digest does the same job with more detail. I did not install or test it here.

log_queries_not_using_indexes: useful, but noisy

The manual says that with this option on, queries that do not use indexes for row lookups are logged even when they are fast. I raised the threshold to 10 seconds and enabled it:

SET GLOBAL long_query_time = 10;
SET GLOBAL log_queries_not_using_indexes = 1;
Enter fullscreen mode Exit fullscreen mode
SELECT id FROM orders WHERE user_id = 1234;
SELECT id FROM users WHERE country = 'LK' LIMIT 3;
Enter fullscreen mode Exit fullscreen mode

Those two were logged. Primary key lookups (WHERE id = 5 on either table) were not. On a busy system with many small tables this can flood the log, which is why the manual describes log_throttle_queries_not_using_indexes, a per-minute cap. With it set to 2, five different non-indexed queries produced 2 log entries. The manual says suppressed queries are summarized later in the log. Treat the option as a periodic audit and not something to leave on permanently.

No log file? Use the performance schema

If you can't change server settings or restart, the performance_schema keeps per-statement-shape totals in memory. In my run it was enabled by default:

SELECT COUNT_STAR, SUM_ROWS_EXAMINED, SUM_ROWS_SENT, SUM_NO_INDEX_USED,
       LEFT(DIGEST_TEXT, 78)
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'shop'
  AND DIGEST_TEXT LIKE 'SELECT%orders%'
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 5;
Enter fullscreen mode Exit fullscreen mode
3 | 1500060 | 60 | 3 | SELECT * FROM `orders` WHERE `user_id` = ? ORDER BY `created_at` DESC LIMIT ?
1 | 500000  | 1  | 1 | SELECT COUNT ( * ) FROM `orders` WHERE DATE ( `created_at` ) = ?
Enter fullscreen mode Exit fullscreen mode

Sorting by rows examined puts the worst offenders first. It tells the same story as the slow log: three calls, one and a half million rows examined, sixty rows returned, no index used. The counters reset when the server restarts and can be cleared with TRUNCATE on the summary table, so it is good for "what is hot right now" and weaker for history.

Catch slow queries from Laravel

The database log shows what MySQL saw. The application log can add the request, the user and the code path. Laravel's DB::listen receives every query with its time in milliseconds. In AppServiceProvider::boot():

use Illuminate\Database\Events\QueryExecuted;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

public function boot(): void
{
    DB::listen(function (QueryExecuted $query) {
        if ($query->time > 500) { // milliseconds
            Log::warning('Slow query', [
                'ms' => $query->time,
                'sql' => $query->sql,
                'bindings' => $query->bindings,
            ]);
        }
    });
}
Enter fullscreen mode Exit fullscreen mode

I ran the same listener with a 30 ms threshold. The slow query was logged and the primary key lookup was not:

local.WARNING: Slow query {"ms":<ms>,"sql":"select * from `orders` where `user_id` = ? order by `created_at` desc limit 20","bindings":[1234]}
Enter fullscreen mode Exit fullscreen mode

Be careful with the similarly named DB::whenQueryingForLongerThan. Reading its source in Laravel 13.40, it compares the connection's total query duration so far, not a single query, and runs its handler once. I ran three queries of roughly 130 ms each with a 150 ms threshold, and the handler fired exactly once:

cumulative threshold crossed; last query: select * from `orders` where `user_id` = ? order by `created_at` desc limit 20
queries run: 3; handler fires once
Enter fullscreen mode Exit fullscreen mode

That is useful for "this request spent too long in the database overall". For a single slow statement, use DB::listen.

When you have the builder in hand, toRawSql() gives you a statement you can paste into a MySQL session, and explain() runs EXPLAIN for you:

$q = DB::table('orders')->where('user_id', 1234)->orderByDesc('created_at')->limit(20);

$q->toRawSql();
// select * from `orders` where `user_id` = 1234 order by `created_at` desc limit 20

$row = $q->explain()->first();
// type=ALL key=NULL Extra=Using where; Using filesort
Enter fullscreen mode Exit fullscreen mode

Never log raw bindings for queries that touch passwords, tokens or personal data unless your log storage is as protected as your database. The MySQL manual says passwords are rewritten in the slow log, but ordinary values such as emails and names are written as they are.

Production considerations

  • Rotate the file. Move the old log away and run FLUSH SLOW LOGS so MySQL opens a new one. I used this between test runs and it produced a clean file each time. Set up log rotation so the file does not grow without limit.
  • Sample before you tune. Collect at least a full business cycle (a day, a week with month-end) before deciding what is worst. A nightly report and a hot endpoint look very different.
  • Read access. The log contains real literals from real queries. Limit who can read it.
  • Cost of logging. I did not measure the overhead of the log. With a sensible threshold it only writes the slow statements, but a very low threshold or log_queries_not_using_indexes can write a lot.
  • Cloud databases. Managed services (RDS, Cloud SQL and others) usually set these through parameter groups or flags and may write to a table or a managed log. I did not test any; the manual notes that log_output can send the log to a table as well as a file.

Common mistakes

  • Leaving long_query_time at the default of 10 seconds and concluding the application has no slow queries.
  • Lowering the threshold at runtime and not restarting long-lived workers.
  • Reading single entries instead of grouping with mysqldumpslow or the digest table, and chasing a one-off while the frequent query goes unnoticed.
  • Judging a query only by Query_time. Compare Rows_examined with Rows_sent.
  • Leaving log_queries_not_using_indexes on permanently.

Alternatives

  • pt-query-digest for richer reports from the same log file (not tested here).
  • Application performance monitoring tools that sample queries with request context.
  • Thesysschema views over the performance schema. I only queried the digest table directly.

Related Dev Talk articles

Frequently asked questions

What long_query_time should I use?

Start with 1 second and look at the log after a normal week. If it is empty, lower it. If it is overwhelming, fix the top few statements first and keep the threshold. The right value is whatever your slowest acceptable query is.

Why is a query logged that runs fast when I try it?

Caching, a different parameter value, or contention at the time it ran. Copy the exact statement with its literals from the log and run it, and look at Rows_examined, which does not depend on timing.

Does the slow log show queries that were killed or failed?

I did not test this. The manual says statements that cannot be parsed, such as syntax errors, are not logged as of MySQL 8.0.29.

Is the slow log the same on MariaDB?

The concept is the same. I tested MySQL 8.0 only, so check the MariaDB documentation for variable names and defaults.

Sources


Originally published on DEV Talk.

Top comments (0)