<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="slow-query-log">
      </a>
      7.4.5 The Slow Query Log
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045257590816">
  </a>
  <a class="indexterm" name="idm46045257589744">
  </a>
  <p>
   The slow query log consists of SQL statements that take more than
   <a class="link" href="server-system-variables.html#sysvar_long_query_time">
    <code class="literal">
     long_query_time
    </code>
   </a>
   seconds to
      execute and require at least
   <a class="link" href="server-system-variables.html#sysvar_min_examined_row_limit">
    <code class="literal">
     min_examined_row_limit
    </code>
   </a>
   rows to be
      examined. 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 be a
      time-consuming task. To make this easier, you can use the
   <a class="link" href="mysqldumpslow.html" title="6.6.10 mysqldumpslow — Summarize Slow Query Log Files">
    <span class="command">
     <strong>
      mysqldumpslow
     </strong>
    </span>
   </a>
   command to process a slow query
      log file and summarize its contents. See
   <a class="xref" href="mysqldumpslow.html" title="6.6.10 mysqldumpslow — Summarize Slow Query Log Files">
    Section 6.6.10, “mysqldumpslow — Summarize Slow Query Log Files”
   </a>
   .
  </p>
  <p>
   The time to acquire the initial locks is not counted as execution
      time.
   <a class="link" href="mysqld.html" title="6.3.1 mysqld — The MySQL Server">
    <span class="command">
     <strong>
      mysqld
     </strong>
    </span>
   </a>
   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.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="slow-query-log.html#slow-query-log-parameters" title="Slow Query Log Parameters">
       Slow Query Log Parameters
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="slow-query-log.html#slow-query-log-contents" title="Slow Query Log Contents">
       Slow Query Log Contents
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="slow-query-log-parameters">
       </a>
       Slow Query Log Parameters
      </h4>
     </div>
    </div>
   </div>
   <p>
    The minimum and default values of
    <a class="link" href="server-system-variables.html#sysvar_long_query_time">
     <code class="literal">
      long_query_time
     </code>
    </a>
    are 0 and 10,
        respectively. The value can be specified to a resolution of
        microseconds.
   </p>
   <p>
    By default, administrative statements are not logged, nor are
        queries that do not use indexes for lookups. This behavior can
        be changed using
    <a class="link" href="server-system-variables.html#sysvar_log_slow_admin_statements">
     <code class="literal">
      log_slow_admin_statements
     </code>
    </a>
    and
    <a class="link" href="server-system-variables.html#sysvar_log_queries_not_using_indexes">
     <code class="literal">
      log_queries_not_using_indexes
     </code>
    </a>
    ,
        as described later.
   </p>
   <p>
    By default, the slow query log is disabled. To specify the
        initial slow query log state explicitly, use
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
     <code class="option">
      --slow_query_log[={0|1}]
     </code>
    </a>
    . With no
        argument or an argument of 1,
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
     <code class="option">
      --slow_query_log
     </code>
    </a>
    enables the log.
        With an argument of 0, this option disables the log. To specify
        a log file name, use
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log_file">
     <code class="option">
      --slow_query_log_file=
      <em class="replaceable">
       <code>
        file_name
       </code>
      </em>
     </code>
    </a>
    .
        To specify the log destination, use the
    <a class="link" href="server-system-variables.html#sysvar_log_output">
     <code class="literal">
      log_output
     </code>
    </a>
    system variable (as
        described in
    <a class="xref" href="log-destinations.html" title="7.4.1 Selecting General Query Log and Slow Query Log Output Destinations">
     Section 7.4.1, “Selecting General Query Log and Slow Query Log Output Destinations”
    </a>
    ).
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     If you specify the
     <code class="literal">
      TABLE
     </code>
     log destination,
          see
     <a class="xref" href="log-destinations.html#log-destinations-tables-open-files" title="Log Tables and “Too many open files” Errors">
      Log Tables and
      <span class="quote">
       “
       <span class="quote">
        Too many open files
       </span>
       ”
      </span>
      Errors
     </a>
     .
    </p>
   </div>
   <p>
    If you specify no name for the slow query log file, the default
        name is
    <code class="filename">
     <em class="replaceable">
      <code>
       host_name
      </code>
     </em>
     -slow.log
    </code>
    .
        The server creates the file in the data directory unless an
        absolute path name is given to specify a different directory.
   </p>
   <p>
    To disable or enable the slow query log or change the log file
        name at runtime, use the global
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
     <code class="literal">
      slow_query_log
     </code>
    </a>
    and
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log_file">
     <code class="literal">
      slow_query_log_file
     </code>
    </a>
    system
        variables. Set
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log">
     <code class="literal">
      slow_query_log
     </code>
    </a>
    to 0 to disable the log or to 1 to enable it. Set
    <a class="link" href="server-system-variables.html#sysvar_slow_query_log_file">
     <code class="literal">
      slow_query_log_file
     </code>
    </a>
    to specify
        the name of the log file. If a log file already is open, it is
        closed and the new file is opened.
   </p>
   <p>
    The server writes less information to the slow query log if you
        use the
    <a class="link" href="server-options.html#option_mysqld_log-short-format">
     <code class="option">
      --log-short-format
     </code>
    </a>
    option.
   </p>
   <p>
    To include slow administrative statements in the slow query log,
        enable the
    <a class="link" href="server-system-variables.html#sysvar_log_slow_admin_statements">
     <code class="literal">
      log_slow_admin_statements
     </code>
    </a>
    system variable. Administrative statements include
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    ,
    <a class="link" href="analyze-table.html" title="15.7.3.1 ANALYZE TABLE Statement">
     <code class="literal">
      ANALYZE TABLE
     </code>
    </a>
    ,
    <a class="link" href="check-table.html" title="15.7.3.2 CHECK TABLE Statement">
     <code class="literal">
      CHECK TABLE
     </code>
    </a>
    ,
    <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
     <code class="literal">
      CREATE INDEX
     </code>
    </a>
    ,
    <a class="link" href="drop-index.html" title="15.1.27 DROP INDEX Statement">
     <code class="literal">
      DROP INDEX
     </code>
    </a>
    ,
    <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
     <code class="literal">
      OPTIMIZE TABLE
     </code>
    </a>
    , and
    <a class="link" href="repair-table.html" title="15.7.3.5 REPAIR TABLE Statement">
     <code class="literal">
      REPAIR TABLE
     </code>
    </a>
    .
   </p>
   <p>
    To include queries that do not use indexes for row lookups in
        the statements written to the slow query log, enable the
    <a class="link" href="server-system-variables.html#sysvar_log_queries_not_using_indexes">
     <code class="literal">
      log_queries_not_using_indexes
     </code>
    </a>
    system variable. (Even with that variable enabled, the server
        does not log queries that would not benefit from the presence of
        an index due to the table having fewer than two rows.)
   </p>
   <p>
    When queries that do not use an index are logged, the slow query
        log may grow quickly. It is possible to put a rate limit on
        these queries by setting the
    <a class="link" href="server-system-variables.html#sysvar_log_throttle_queries_not_using_indexes">
     <code class="literal">
      log_throttle_queries_not_using_indexes
     </code>
    </a>
    system variable. By default, this variable is 0, which means
        there is no limit. Positive values impose a per-minute limit on
        logging of queries that do not use indexes. The first such query
        opens a 60-second window within which the server logs queries up
        to the given limit, then suppresses additional queries. If there
        are suppressed queries when the window ends, the server logs a
        summary that indicates how many there were and the aggregate
        time spent in them. The next 60-second window begins when the
        server logs the next query that does not use indexes.
   </p>
   <p>
    The server uses the controlling parameters in the following
        order to determine whether to write a query to the slow query
        log:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       The query must either not be an administrative statement, or
       <a class="link" href="server-system-variables.html#sysvar_log_slow_admin_statements">
        <code class="literal">
         log_slow_admin_statements
        </code>
       </a>
       must be enabled.
      </p>
     </li>
     <li class="listitem">
      <p>
       The query must have taken at least
       <a class="link" href="server-system-variables.html#sysvar_long_query_time">
        <code class="literal">
         long_query_time
        </code>
       </a>
       seconds, or
       <a class="link" href="server-system-variables.html#sysvar_log_queries_not_using_indexes">
        <code class="literal">
         log_queries_not_using_indexes
        </code>
       </a>
       must be enabled and the query used no indexes for row
            lookups.
      </p>
     </li>
     <li class="listitem">
      <p>
       The query must have examined at least
       <a class="link" href="server-system-variables.html#sysvar_min_examined_row_limit">
        <code class="literal">
         min_examined_row_limit
        </code>
       </a>
       rows.
      </p>
     </li>
     <li class="listitem">
      <p>
       The query must not be suppressed according to the
       <a class="link" href="server-system-variables.html#sysvar_log_throttle_queries_not_using_indexes">
        <code class="literal">
         log_throttle_queries_not_using_indexes
        </code>
       </a>
       setting.
      </p>
     </li>
    </ol>
   </div>
   <p>
    The
    <a class="link" href="server-system-variables.html#sysvar_log_timestamps">
     <code class="literal">
      log_timestamps
     </code>
    </a>
    system
        variable controls the time zone of timestamps in messages
        written to the slow query log file (as well as to the general
        query log file and the error log). It does not affect the time
        zone of general query log and slow query log messages written to
        log tables, but rows retrieved from those tables can be
        converted from the local system time zone to any desired time
        zone with
    <a class="link" href="date-and-time-functions.html#function_convert-tz">
     <code class="literal">
      CONVERT_TZ()
     </code>
    </a>
    or by
        setting the session
    <a class="link" href="server-system-variables.html#sysvar_time_zone">
     <code class="literal">
      time_zone
     </code>
    </a>
    system variable.
   </p>
   <p>
    By default, a replica does not write replicated queries to the
        slow query log. To change this, enable the
    <a class="link" href="replication-options-replica.html#sysvar_log_slow_replica_statements">
     <code class="literal">
      log_slow_replica_statements
     </code>
    </a>
    system variable. Note that if row-based replication is in use
        (
    <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
     <code class="literal">
      binlog_format=ROW
     </code>
    </a>
    ), these
        system variables have no effect. Queries are only added to the
        replica's slow query log when they are logged in statement
        format in the binary log, that is, when
    <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
     <code class="literal">
      binlog_format=STATEMENT
     </code>
    </a>
    is set,
        or when
    <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
     <code class="literal">
      binlog_format=MIXED
     </code>
    </a>
    is
        set and the statement is logged in statement format. Slow
        queries that are logged in row format when
    <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
     <code class="literal">
      binlog_format=MIXED
     </code>
    </a>
    is set, or
        that are logged when
    <a class="link" href="replication-options-binary-log.html#sysvar_binlog_format">
     <code class="literal">
      binlog_format=ROW
     </code>
    </a>
    is set, are
        not added to the replica's slow query log, even if
    <a class="link" href="replication-options-replica.html#sysvar_log_slow_replica_statements">
     <code class="literal">
      log_slow_replica_statements
     </code>
    </a>
    is
        enabled.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="slow-query-log-contents">
       </a>
       Slow Query Log Contents
      </h4>
     </div>
    </div>
   </div>
   <p>
    When the slow query log is enabled, the server writes output to
        any destinations specified by the
    <a class="link" href="server-system-variables.html#sysvar_log_output">
     <code class="literal">
      log_output
     </code>
    </a>
    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
    <code class="literal">
     FILE
    </code>
    log
        destination is selected. If the destination is
    <code class="literal">
     NONE
    </code>
    , the server writes no queries even if
        the slow query log is enabled. Setting the log file name has no
        effect on logging if
    <code class="literal">
     FILE
    </code>
    is not selected as
        an output destination.
   </p>
   <p>
    If the slow query log is enabled and
    <code class="literal">
     FILE
    </code>
    is
        selected as an output destination, each statement written to the
        log is preceded by a line that begins with a
    <code class="literal">
     #
    </code>
    character and has these fields (with all
        fields on a single line):
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        Query_time:
        <em class="replaceable">
         <code>
          duration
         </code>
        </em>
       </code>
      </p>
      <p>
       The statement execution time in seconds.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Lock_time:
        <em class="replaceable">
         <code>
          duration
         </code>
        </em>
       </code>
      </p>
      <p>
       The time to acquire locks in seconds.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Rows_sent:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The number of rows sent to the client.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Rows_examined:
        <em class="replaceable">
         <code>
         </code>
        </em>
       </code>
      </p>
      <p>
       The number of rows examined by the server layer (not
            counting any processing internal to storage engines).
      </p>
     </li>
    </ul>
   </div>
   <p>
    Enabling the
    <a class="link" href="server-system-variables.html#sysvar_log_slow_extra">
     <code class="literal">
      log_slow_extra
     </code>
    </a>
    system variable causes the server to write the following extra
        fields to
    <code class="literal">
     FILE
    </code>
    output in addition to those
        just listed (
    <code class="literal">
     TABLE
    </code>
    output is unaffected).
        Some field descriptions refer to status variable names. Consult
        the status variable descriptions for more information. However,
        in the slow query log, the counters are per-statement values,
        not cumulative per-session values.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        Thread_id:
        <em class="replaceable">
         <code>
          ID
         </code>
        </em>
       </code>
      </p>
      <p>
       The statement thread identifier.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Errno:
        <em class="replaceable">
         <code>
          error_number
         </code>
        </em>
       </code>
      </p>
      <p>
       The statement error number, or 0 if no error occurred.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Killed:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       If the statement was terminated, the error number indicating
            why, or 0 if the statement terminated normally.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Bytes_received:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Bytes_received">
        <code class="literal">
         Bytes_received
        </code>
       </a>
       value
            for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Bytes_sent:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Bytes_sent">
        <code class="literal">
         Bytes_sent
        </code>
       </a>
       value for
            the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_first:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_first">
        <code class="literal">
         Handler_read_first
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_last:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_last">
        <code class="literal">
         Handler_read_last
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_key:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_key">
        <code class="literal">
         Handler_read_key
        </code>
       </a>
       value
            for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_next:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_next">
        <code class="literal">
         Handler_read_next
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_prev:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_prev">
        <code class="literal">
         Handler_read_prev
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_rnd:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_rnd">
        <code class="literal">
         Handler_read_rnd
        </code>
       </a>
       value
            for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Read_rnd_next:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Handler_read_rnd_next">
        <code class="literal">
         Handler_read_rnd_next
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Sort_merge_passes:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Sort_merge_passes">
        <code class="literal">
         Sort_merge_passes
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Sort_range_count:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Sort_range">
        <code class="literal">
         Sort_range
        </code>
       </a>
       value for
            the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Sort_rows:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Sort_rows">
        <code class="literal">
         Sort_rows
        </code>
       </a>
       value for
            the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Sort_scan_count:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Sort_scan">
        <code class="literal">
         Sort_scan
        </code>
       </a>
       value for
            the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Created_tmp_disk_tables:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Created_tmp_disk_tables">
        <code class="literal">
         Created_tmp_disk_tables
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Created_tmp_tables:
        <em class="replaceable">
         <code>
          N
         </code>
        </em>
       </code>
      </p>
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Created_tmp_tables">
        <code class="literal">
         Created_tmp_tables
        </code>
       </a>
       value for the statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        Start:
        <em class="replaceable">
         <code>
          timestamp
         </code>
        </em>
       </code>
      </p>
      <p>
       The statement execution start time.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        End:
        <em class="replaceable">
         <code>
          timestamp
         </code>
        </em>
       </code>
      </p>
      <p>
       The statement execution end time.
      </p>
     </li>
    </ul>
   </div>
   <p>
    A given slow query log file may contain a mix of lines with and
        without the extra fields added by enabling
    <a class="link" href="server-system-variables.html#sysvar_log_slow_extra">
     <code class="literal">
      log_slow_extra
     </code>
    </a>
    . Log file
        analyzers can determine whether a line contains the additional
        fields by the field count.
   </p>
   <p>
    Each statement written to the slow query log file is preceded by
        a
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
     </code>
    </a>
    statement that includes a timestamp, which indicates when the
        slow statement began executing.
   </p>
   <p>
    Passwords in statements written to the slow query log are
        rewritten by the server not to occur literally in plain text.
        See
    <a class="xref" href="password-logging.html" title="8.1.2.3 Passwords and Logging">
     Section 8.1.2.3, “Passwords and Logging”
    </a>
    .
   </p>
   <p>
    Statements that cannot be parsed (due, for example, to syntax
        errors) are not written to the slow query log.
   </p>
  </div>
 </div>
 <br/>
</div>
