"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
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
Change it without a restart
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
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";
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;
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
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'
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;
SELECT id FROM orders WHERE user_id = 1234;
SELECT id FROM users WHERE country = 'LK' LIMIT 3;
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;
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` ) = ?
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,
]);
}
});
}
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]}
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
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
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 LOGSso 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_indexescan 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_outputcan send the log to a table as well as a file.
Common mistakes
- Leaving
long_query_timeat 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
mysqldumpslowor the digest table, and chasing a one-off while the frequent query goes unnoticed. - Judging a query only by
Query_time. CompareRows_examinedwithRows_sent. - Leaving
log_queries_not_using_indexeson permanently.
Alternatives
-
pt-query-digestfor richer reports from the same log file (not tested here). - Application performance monitoring tools that sample queries with request context.
-
The
sysschema views over the performance schema. I only queried the digest table directly.
Related Dev Talk articles
- MySQL Slow Query Log and EXPLAIN, Part 2: Read EXPLAIN and Fix Slow Queries with Indexes
- Laravel Redis Caching Patterns, Part 1: Cache-Aside, Key Design, Invalidation and Tags
- PostgreSQL vs MySQL: Performance Comparison (Tested on the Same Machine)
- MySQL "Too many connections" (Error 1040): Causes and How to Fix It
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
- MySQL 8.0 Reference Manual: The Slow Query Log
- laravel/framework source: Connection::whenQueryingForLongerThan and listen
Originally published on DEV Talk.
Top comments (0)