How to Enable and Configure the MySQL Slow Query Log

Quick Answer

On MySQL 8.0, an administrator can switch on the slow query log right away with these statements:

SET PERSIST slow_query_log = ON;
SET PERSIST log_output = 'FILE';
SET PERSIST long_query_time = 1;

Any query that runs longer than one second will be recorded. Check the active settings with:

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

One catch: Reconnect your MySQL client before you test. A global change to long_query_time applies to new sessions, but an existing connection may keep the old session value.

What MySQL Writes to the Slow Query Log

The MySQL slow query log captures SQL statements that run past the configured execution-time threshold. A normal entry shows the query duration and lock time, along with rows examined, rows returned, the client account, and the SQL statement itself.

This is useful when an application feels sluggish and you don’t yet know why. Missing indexes may show up. So can excessive table scans, inefficient joins, or queries that chew through far more rows than they return.

Many installations leave the slow query log disabled by default. Continuous logging uses storage and adds a small amount of I/O overhead, so that default is generally understandable.

Before You Start

• Administrative MySQL access, such as an account with SYSTEM_VARIABLES_ADMIN on MySQL 8.0.
• Shell access if you’re going to edit the MySQL configuration file.
• A writable log directory that the MySQL service account owns or can access.
• Enough free disk space for the query volume you expect.

These steps are for self-managed MySQL servers. Amazon RDS, Azure Database for MySQL, and similar managed services usually put slow-log controls in parameter groups or provider settings rather than the server configuration file.

Method 1 — Turn On the Log at Runtime

MySQL 8.0 supports SET PERSIST. It changes the live setting, then writes that value to mysqld-auto.cnf so the change remains after a restart.

1. Connect with an administrative account:

   mysql -u root -p

2. Enable slow-query logging and send the output to a file:

   SET PERSIST log_output = 'FILE';
   SET PERSIST slow_query_log = ON;

3. Set the threshold to one second:

   SET PERSIST long_query_time = 1;

4. Find the log file MySQL will use:

   SHOW VARIABLES LIKE 'slow_query_log_file';

For a temporary test, use SET GLOBAL instead. These settings disappear when MySQL restarts:

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

MySQL 5.7 doesn’t support SET PERSIST. Use SET GLOBAL for the immediate change, then edit the server configuration if you need the setting to survive a restart.

Method 2 — Set It in my.cnf

Configuration-file settings tend to be easier to audit, especially when server automation is involved. On Ubuntu and Debian, a common location is /etc/mysql/mysql.conf.d/mysqld.cnf. RHEL-based systems often use /etc/my.cnf.

Under the [mysqld] section, add the following options:

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
log_output = FILE
long_query_time = 1
min_examined_row_limit = 100
log_queries_not_using_indexes = OFF

The optional min_examined_row_limit setting keeps very small queries out of the log, even if they cross the time threshold. On a busy server, that can cut down quite a bit of noise.

If the installed MySQL version supports configuration validation, run:

sudo mysqld --validate-config

Then restart the service:

sudo systemctl restart mysql

Some distributions call the service mysqld instead. In that case, use:

sudo systemctl restart mysqld

Picking a long_query_time Value

One or two seconds is a sensible place to begin. Look at what the log captures, then lower the threshold if you still need more detail. Don’t leave a zero-second threshold running in production.

Check That Logging Actually Works

1. Disconnect and reconnect so the session picks up the current threshold.

2. Run a deliberately slow statement:

   SELECT SLEEP(2);

3. Read the configured log file:

   sudo tail -n 50 /var/log/mysql/mysql-slow.log

Your server may use a different path. Retrieve the real one with SHOW VARIABLES LIKE 'slow_query_log_file';. If the test statement runs longer than long_query_time, it should appear in the file.

Reading the Slow Query Log

MySQL ships with mysqldumpslow, a tool that groups similar statements. To list the ten queries with the highest average execution time, run:

sudo mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

To sort them by total query time instead, use:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

Once you’ve found a troublesome statement, inspect its execution plan:

EXPLAIN ANALYZE
SELECT ...;

Be careful here. EXPLAIN ANALYZE really executes the query, which matters for expensive statements and data-changing operations. Older MySQL versions should use EXPLAIN without ANALYZE.

Logging Queries That Skip Indexes

The log_queries_not_using_indexes option records qualifying queries that don’t use an index, even if they finish quickly:

SET PERSIST log_queries_not_using_indexes = ON;

Use this only for a focused investigation. A query against a small table may be perfectly right to avoid an index, so the setting can fill the log with misleading entries. Pair it with min_examined_row_limit to filter out trivial table scans.

Mistakes That Cause Trouble

• Testing through an old connection: An existing session may still have its earlier long_query_time value.
• Expecting SET GLOBAL to persist: Runtime global settings normally vanish after a restart.
• Choosing a path MySQL can’t write to: The server can’t create the log unless its service account has permission to use the directory.
• Leaving an aggressive threshold enabled: A large slow log can eat through the server’s available disk space.
• Assuming every logged query is bad: Reports, backups, maintenance jobs, and deliberately large queries may have legitimate reasons to run longer.

Practical Habits

• Watch the log file’s size and configure operating-system log rotation.
• For most production investigations, use FILE output. Table-based logging adds database overhead and is harder to rotate.
• Collect data during representative traffic periods instead of judging everything from one test.
• Check execution plans before adding indexes. Every extra index takes storage and makes writes more expensive.
• After a temporary investigation, turn the log off if you don’t need continuous monitoring:

SET PERSIST slow_query_log = OFF;