Why is the slow query log empty while users see latency?

Quick answer: The slow query log ships disabled, and when you turn it on the threshold is ten seconds, so a two second query is nowhere near it. Turn it on, drop long_query_time well under a second, and set it with SET SESSION in the connection you are testing from, because SET GLOBAL does not reach a session that is already open.

What is the problem?

There is real latency, somebody checks the slow query log, and it has nothing in it. The conclusion is that the database is fine.

That conclusion does not follow. An empty slow query log is consistent with a database that is fine and with one that is not. Six things can empty it: the threshold, the session that read the threshold, the log destination, the file you are reading, the kind of statement involved, and time spent outside what the log measures. This page checks all six in order.

The defaults

The MySQL 9.7 manual on the slow query log, read 2026-09-20, says “By default, the slow query log is disabled” and “The minimum and default values of long_query_time are 0 and 10, respectively”.

A stock mysql:lts container, MySQL 9.7.2 in container dbg-mysqlslow, measured 2026-09-20 with nothing configured:

slow_query_log                  OFF
long_query_time                 10.000000
log_output                      FILE
log_slow_extra                  OFF
min_examined_row_limit          0
log_queries_not_using_indexes   OFF
log_slow_admin_statements       OFF
log_slow_replica_statements     OFF

Those two settings explain most empty logs. The log is off. If it were on, the threshold would be ten seconds.

Read them on your own server rather than relying on a configuration file, because a packaged, managed or vendor-modified install can start with different values, and a file that was edited but never reloaded looks identical to one that took effect:

SHOW VARIABLES WHERE Variable_name IN (
  'slow_query_log',
  'slow_query_log_file',
  'long_query_time',
  'log_output',
  'log_slow_extra',
  'min_examined_row_limit',
  'log_queries_not_using_indexes',
  'log_slow_admin_statements'
);

Turn it on, and prove it works before you trust an absence

log_output = TABLE sends entries to mysql.slow_log, which you can query with SQL instead of looking for a file:

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET SESSION long_query_time = 0.5;

That last line matters because leaving it out is the most common way this test fails. The next section explains why.

Now produce a statement you know is slow:

SELECT SLEEP(1);

And read it back:

SELECT start_time,
       query_time,
       lock_time,
       rows_examined,
       LEFT(CONVERT(sql_text USING utf8mb4), 60) AS sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

On MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, that returned one row for the sleep with query_time of 00:00:01.000203, and nothing for the fast statement that followed. The log was working. If your own statement is still missing, the six reasons below are the remaining possibilities.

The six reasons it stays empty

1. The threshold is above the pain

Ten seconds is the documented default, but most users notice delays sooner. A query taking 800 milliseconds a hundred times a minute can be a bigger problem than one taking twelve seconds once an hour.

The default is worth checking rather than taking on trust. One connection, id 55, opened while the global value was still 10, on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, with the log on and log_output at TABLE:

@@session.long_query_time	statement	rows in mysql.slow_log
10.000000	SELECT SLEEP(2)	0
0.500000	SELECT SLEEP(2)	1

The logged row reads 00:00:02.000189. The connection, the statement and the two seconds of waiting were the same; only the threshold changed.

Set it to 0.5, or lower, and watch the volume before leaving it there.

2. SET GLOBAL did not reach the connection you are testing from

This is a common reason a setting appears not to work, and it is worth measuring rather than asserting.

One connection was opened while the global value was still 10. Then, inside that same connection, on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20:

step	session value	global value	logged?
at connect	10.000000	10.000000
after SET GLOBAL long_query_time = 0.5	10.000000	0.500000
SELECT SLEEP(1)	10.000000	0.500000	no, mysql.slow_log empty
after SET SESSION long_query_time = 0.5	0.500000	0.500000
SELECT SLEEP(1)	0.500000	0.500000	yes, 00:00:01.000203

A new connection opened after the SET GLOBAL logged its sleep at 00:00:01.000520, on the same server and day. The global value is the default a session inherits when it starts. It does not change sessions that already exist. A pooled application may hold its connections for hours, so the change can appear to do nothing after you make it.

The session value is not frozen. SET SESSION long_query_time changes it immediately in the connection that runs it, which makes it the right way to test. Check both values before drawing a conclusion:

SELECT @@global.long_query_time  AS global_value,
       @@session.long_query_time AS session_value;

3. log_output is NONE

Turning the slow query log on does not specify where it goes, and NONE discards the entries.

An empty log does not distinguish this case from reason 2. Change the destination while a session is already logging, then change it back. One connection, id 53, on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, with slow_query_log ON and @@session.long_query_time at 0.500000 at every step:

log_output	statement	rows in mysql.slow_log
TABLE	SELECT SLEEP(1)	1
NONE	SELECT SLEEP(1)	1
TABLE	SELECT SLEEP(1)	2

The two logged rows read 00:00:01.000199 and 00:00:01.000183. The middle sleep took the same second and left nothing.

The session was logging before and after the change, so the gap in the middle comes from the destination rather than from a stale threshold.

Check log_output in the same breath as slow_query_log.

4. You are reading the wrong file

With log_output = FILE, entries go to slow_query_log_file. The manual, read 2026-09-20 at the slow query log page, says: “If you specify no name for the slow query log file, the default name is host_name-slow.log.”

That is the server’s resolved host name, not a container rule, though in a container it will look like a container id: the fresh container above reported /var/lib/mysql/4745dae8564f-slow.log. On a managed database the file may not be reachable at all, and the console has its own view of it.

Do not infer the path. Ask for it:

SELECT @@global.slow_query_log_file;

log_output = TABLE avoids the question as long as the log is enabled, your account can read mysql.slow_log, and you are querying the same server that wrote it. Table logging is better treated as an investigation setting than as something to leave on a busy server indefinitely, but this page has not benchmarked its cost against FILE and does not claim a figure for it.

5. The statement is a kind that is not logged

Administrative statements are excluded by default. The manual, read 2026-09-20, gives the full set: “Administrative statements include ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE.”

Measured on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, against a one million row table with long_query_time at 0.5 and ALGORITHM=COPY:

log_slow_admin_statements	statement	wall clock	logged
OFF	ALTER TABLE t ADD COLUMN pad ...	4.332 s	no
ON	ALTER TABLE t DROP COLUMN pad ...	2.959 s	yes, 00:00:02.884469

On MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, that is 4.332 seconds of real work against a 0.5 second threshold, more than eight times over it, with nothing in the log.

Replicated statements are excluded by default too, and turning them on is not enough on a modern topology. From the slow query log page, read 2026-09-20: “By default, a replica does not write replicated queries to the slow query log. To change this, enable the log_slow_replica_statements system variable. Note that if row-based replication is in use (binlog_format=ROW), these system variables have no effect.”

That sentence predicts why a setting can appear not to work. It was tested on a replication pair on MySQL 9.7.2, containers dbg-myrep-src and dbg-myrep-rep, 2026-09-20, with the replica on slow_query_log ON, log_output TABLE, long_query_time 0.5 and log_slow_replica_statements ON. The same UPDATE over 500,000 rows each time, once under each binary log format:

binlog_format on the source	apply time	replica mysql.slow_log
ROW	3.356 s	empty
STATEMENT	3.381 s	00:00:02.700731, 500000 rows examined

Before either run, SELECT SLEEP(1) executed directly on the replica was logged, so the replica’s own slow log was working and the empty cell is not a broken instrument.

SELECT @@binlog_format on that server returns ROW, which is the default. The replica is therefore applying row events rather than statements, and log_slow_replica_statements has nothing to log however you set it. The setting is not broken. It applies to a format the default does not use.

The applier threads have to be restarted for any of this to take effect. The first attempt at the table above found nothing logged under either format, because the applier threads had started before the SET GLOBAL and were still holding long_query_time 10. That is reason 2 again, on connections you did not open. STOP REPLICA; START REPLICA; is what made the STATEMENT row appear.

Finally, min_examined_row_limit drops any statement that looked at fewer rows than its value, however long the statement took. Connection id 56, same server and day, long_query_time at 0.5 throughout:

min_examined_row_limit	statement	rows in mysql.slow_log
100	SELECT SLEEP(2)	0
0	SELECT SLEEP(2)	1

The logged row reads query_time 00:00:02.000168 against rows_examined of 1. The statement waited two seconds and touched one row, and a limit of 100 was enough to discard it.

6. The time is real and it is not in the statement

This is the case where the log is telling the truth.

The slow query log does not measure everything the server spends time on. From the manual, read 2026-09-20 at the slow query log page: “The time to acquire the initial locks is not counted as execution time.” The log also does not measure anything outside the server: connection acquisition, waiting for a free connection in the application’s pool, TLS handshakes, network round trips, or the application’s own work between queries.

An application making forty fast queries to build one page has a slow page and no slow queries. Measured on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, connection id 57, with the log on and long_query_time at 0.5: forty statements of 20 milliseconds each took 0.8156 seconds inside the server and produced zero rows in mysql.slow_log. A single SELECT SLEEP(0.6) in that same connection immediately afterwards produced one, so the log was working the whole time.

That is eight tenths of a second of database time, and none of it was slow. The log is correct, and the latency is real. No threshold can find this because there is no single statement to find.

What a row lock wait actually does to the numbers

The manual sentence above can be misread as saying that lock waits are invisible here. They are not. The distinction determines whether you spend a day optimising a statement that was never slow.

Two connections. One holds a row lock for two seconds. The other tries to update the same row half a second in. On MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, with long_query_time at 0.5:

query_time	lock_time	rows_examined	statement
00:00:02.000275	00:00:00.000000	1	SELECT SLEEP(2)
00:00:01.502541	00:00:01.496509	1	UPDATE t SET v = v + 100 WHERE id = 1

The blocked UPDATE is logged, and its InnoDB row lock wait is inside query_time. Whatever “the initial locks” excludes, it does not exclude this. The row is also self-consistent: lock_time of 1.496509 out of query_time of 1.502541 leaves about 6 milliseconds of actual work.

Read lock_time against query_time on every slow entry. When lock_time is nearly all of query_time, the statement’s own execution was fast and the event is a blocking problem, so investigate blocking rather than tune the query. The statement still made the user wait a second and a half, but the problem is not the WHERE clause.

The slow query log is not a complete lock-wait trace. For a real investigation, the performance schema’s lock instrumentation and information_schema.innodb_trx show who was blocking whom, which the slow log never records.

The better tool for most of this

The slow query log answers “which individual statements crossed a line”. The performance schema answers “where does the time go”, which is usually the more useful question:

SELECT DIGEST_TEXT,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1000000000000, 3) AS total_seconds,
       ROUND(AVG_TIMER_WAIT / 1000000000, 3) AS avg_ms,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 15;

This is on by default, costs nothing extra to read, and finds the fast query run a hundred thousand times that no threshold will catch. Order by total time rather than by average, because frequent queries often cause more harm than slower queries that run rarely.

SUM_ROWS_EXAMINED against SUM_ROWS_SENT is the other signal in that table: a large ratio means the server read far more rows than it returned, which is what a missing index looks like before it becomes slow enough to notice.

Reset it when you want a clean window rather than everything since startup:

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

Why does AI-generated code cause this?

The cause is generated advice more than generated code. Ask how to find slow queries in MySQL and the answer is the slow query log at its default threshold, because that has been the standard answer for twenty years. It is correct, and it is tuned for a world where ten seconds was the threshold of pain.

The second pattern is a configuration snippet that sets long_query_time but does not mention that the connection being tested keeps its old value. The change then appears not to work and gets reverted.

How do I stop it coming back?

Set long_query_time where your users are, and write down why it is that number.

Verify the log with a deliberate slow statement after every change, from a connection that used SET SESSION. An absence you have not tested is not evidence.

Put the digest query in a runbook, so the first command during a latency incident shows total time rather than outliers.

Measure end to end as well. If the application reports a slow page and the digest table reports no slow statements, that difference is the finding.

Check lock_time against query_time on every slow entry before optimising the statement. Optimising a query that was waiting for a lock is a day spent on the wrong thing.

How DBGorilla helps

DBGorilla can help

The digest query above is a snapshot: everything since the last restart, in one reading. DBGorilla connects read-only and gives your AI coding agent the same shape of data as a time series, with average duration, invocations, total duration, rows sent and rows examined per query fingerprint, plus activity samples showing what each session was doing and waiting on. That is what lets you ask what changed at four o’clock rather than what has been true since the last restart. Engine coverage is listed on the product pages. It surfaces and explains; it does not write to your database.

Start free

FAQ

• Why is my MySQL slow query log empty?

  Most often because it is switched off. The MySQL manual says the slow query log is disabled by default and that long_query_time defaults to 10 seconds, and a fresh MySQL 9.7.2 container measured on 2026-09-20 had exactly those values. So even after turning it on, a two second query that users notice is a long way under the threshold.

• I ran SET GLOBAL long_query_time and then a slow query, and it still logged nothing. Why?

  Because your connection kept its old value. Measured on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, a session that connected while the global was 10 still reported 10 after SET GLOBAL 0.5, and its one second sleep was never logged. SET SESSION long_query_time in that same connection logged it immediately. Test with SET SESSION, or reconnect.

• Does a lock wait show up in the slow query log?

  An InnoDB row lock wait does. Measured on MySQL 9.7.2 in container dbg-mysqlslow, 2026-09-20, an UPDATE blocked on a row lock was logged with query_time 00:00:01.502541 and lock_time 00:00:01.496509. The manual's note that the time to acquire the initial locks is not counted as execution time does not cover this case.

• What should long_query_time be?

  Low enough to catch what users complain about, which means well under a second rather than the documented default of 10. This article uses 0.5. Set it and watch the log volume for a few minutes before leaving it, because on a busy server a very low threshold logs a great deal.

• What if the query itself is fast and users still wait?

  Then the time is not in statement execution and no query log will show it. Connection acquisition, pool waiting, TLS handshakes, network round trips and the application's own work all happen outside what the slow log measures. An empty log with real latency is evidence about where to look next.

Related

• How to find slow queries in MySQL
• How to read a MySQL EXPLAIN plan