The slow query log consists of SQL statements that took more than
long_query_time seconds to
execute and (as of MySQL 5.1.21) required at least
min_examined_row_limit rows to be
examined. The default value of
long_query_time is 10. Beginning
with MySQL 5.1.21, the minimum is 0, and the value can be
specified to a resolution of microseconds. For logging to a file,
times are written including the microseconds part. For logging to
tables, only integer times are written; the microseconds part is
ignored. Prior to MySQL 5.1.21, the minimum value is 1, and the
value for this variable must be an integer.
By default, administrative statements are not logged, nor are
queries that do not use indexes for lookups. This behavior can be
The time to acquire the initial locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order.
Control the slow query log at server startup as follows:
As of MySQL 5.1.6, the destination can be a file or a table,
or both. Start mysqld with the
option to enable the slow query log, and optionally use
--log-output to specify the log
destination (as described in
Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).
As of MySQL 5.1.12, as an alternative to
specify the initial slow query log state. In this case, the
default slow query log file name is used. With no argument or
an argument of 1,
--slow_query_log enables the
log. With an argument of 0, this option disables the log.
As of MySQL 5.1.29, use
enable or disable the slow query log, and optionally
to specify a log file name. The
--log-slow-queries option is
If the slow query log file is enabled but no name is specified,
the default name is
and the server creates the file in the same directory where it
creates the PID file. If a name is given, the server creates the
file in the data directory unless an absolute path name is given
to specify a different directory.
To disable or enable the slow query log or change the log file
name at runtime, use the global
OFF) to disable the log or to 1 (or
ON) to enable it. Set
slow_query_log_file to specify
the name of the log file. If a log file already is open, it is
closed and the new file is opened.
When the slow query log is enabled, the server writes output to
any destinations specified by the
--log-output option or
log_output system variable. If
you enable the log, the server opens the log file and writes
startup messages to it. However, further logging of queries to the
file does not occur unless the
destination is selected. If the destination is
NONE, the server writes no queries even if the
slow query log is enabled. Setting the log file name has no effect
on logging if the log destination value does not contain
The server writes less information to the slow query log (and
binary log) if you use the
To include slow administrative statements in the statements
written to the slow query log, use the
option. Administrative statements include
OPTIMIZE TABLE, and
To include queries that do not use indexes for row lookups in the
statements written to the slow query log, use the
server option. See Section 5.1.3, “Server Command Options”. When such
queries are logged, the slow query log may grow quickly.
The server uses the controlling parameters in the following order to determine whether to write a query to the slow query log:
The query must either not be an administrative statement, or
must have been specified.
The query must have examined at least
The server does not write queries handled by the query cache to the slow query log, nor queries that would not benefit from the presence of an index because the table has zero rows or one row.
Prior to MySQL 5.1.45, replication slaves did not write replicated
queries to the slow query log, even if the same queries were
written to the slow query log on the master. (Bug #23300) In MySQL
5.1.45 and later, this behavior can be overridden using the
The slow query log should be protected because logged statements might contain passwords. See Section 188.8.131.52, “Passwords and Logging”.
The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize the queries that appear in the log. See Section 4.6.8, “mysqldumpslow — Summarize Slow Query Log Files”.