How to Enable and Use the MySQL Slow Query Log

The MySQL slow query log records every query that takes longer than long_query_time seconds to execute. It is the primary tool for identifying which queries are consuming the most time on a production server and is the starting point for any query optimization effort.

Impact

Without the slow query log enabled, the only way to catch slow queries is real-time monitoring of SHOW PROCESSLIST or performance_schema.events_statements_current, both of which require a query to be running right now. The slow query log provides a persistent, aggregatable record of historical performance — essential for diagnosing intermittent slowdowns that do not coincide with when a developer is watching.

Common Causes of Slow Queries Being Logged

1. Full table scans due to missing or unused indexes
2. Large result sets returned without LIMIT
3. Queries running inside long transactions that spend time waiting for locks
4. ORDER BY and GROUP BY operations that cannot use an index and spill to a temporary table
5. JOIN without an index on the join column causing nested-loop full scans
6. Subqueries that are not optimized to use a semi-join or materialization strategy
7. High long_query_time threshold that masks moderately slow queries at P95
8. log_queries_not_using_indexes=ON logging short queries that happen to skip indexes (useful but noisy)

Configuration and Enabling the Slow Query Log

1. Check current settings:

   SHOW VARIABLES LIKE 'slow_query_log%';
   SHOW VARIABLES LIKE 'long_query_time';
   SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
   SHOW VARIABLES LIKE 'min_examined_row_limit';
   

2. Enable the slow query log at runtime (no restart required):

   SET GLOBAL slow_query_log = ON;
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
   
   -- Log queries taking longer than 1 second
   SET GLOBAL long_query_time = 1;
   
   -- Also log queries that don't use indexes
   SET GLOBAL log_queries_not_using_indexes = ON;
   
   -- Suppress logging of trivial full-scans on tiny tables
   SET GLOBAL min_examined_row_limit = 100;
   
   -- Persist across restarts (MySQL 8.0+)
   SET PERSIST slow_query_log = ON;
   SET PERSIST long_query_time = 1;
   SET PERSIST log_queries_not_using_indexes = ON;
   SET PERSIST min_examined_row_limit = 100;
   

   In my.cnf:

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

3. Tune long_query_time to your SLA. A value of 1 second is a useful starting threshold; lower to 0.1 (100 ms) on well-tuned systems to catch the next tier of slow queries. Setting it to 0 logs everything — useful for load testing but too noisy for production.

4. Enable logging of administrative statements (optional):

   SET GLOBAL log_slow_admin_statements = ON;
   

   This captures slow ALTER TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and similar DDL operations.

Reading the Slow Query Log

A typical entry looks like:

# Time: 2025-04-10T14:23:07.123456Z
# User@Host: app[app] @ 10.0.1.5 [10.0.1.5]  Id: 4521
# Query_time: 4.231891  Lock_time: 0.000112  Rows_sent: 1  Rows_examined: 1847293
SET timestamp=1744294987;
SELECT * FROM orders WHERE customer_email = 'alice@example.com' ORDER BY created_at DESC LIMIT 1;

Key fields:

• Query_time — wall-clock seconds the query took
• Lock_time — time spent waiting for locks (subtracting this gives pure execution time)
• Rows_sent — rows returned to the client
• Rows_examined — rows read from storage to produce the result; a high Rows_examined / Rows_sent ratio indicates a missing index or poor predicate

Aggregating and Analyzing the Log

5. Use mysqldumpslow (built-in) for a quick summary:

   # Top 10 queries by total query time
   mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
   
   # Top 10 by average query time
   mysqldumpslow -s at -t 10 /var/log/mysql/slow.log
   
   # Top 10 by total rows examined
   mysqldumpslow -s r -t 10 /var/log/mysql/slow.log
   

   mysqldumpslow normalizes parameter values so similar queries are grouped together.

6. Use pt-query-digest (Percona Toolkit) for production analysis:

   pt-query-digest /var/log/mysql/slow.log > digest.txt
   

   Output shows each normalized query fingerprint with:

   • Total time consumed
   • Query count
   • Min / Avg / Max / P95 / P99 latency
   • Rows examined vs. rows sent ratio
   • The worst example query

   Filter to the last hour:

   pt-query-digest --since=3600 /var/log/mysql/slow.log
   

   Send directly to a MySQL table for dashboarding:

   pt-query-digest --review h=localhost,D=percona,t=query_review \
     --history h=localhost,D=percona,t=query_history \
     /var/log/mysql/slow.log
   

7. Use performance_schema as an alternative (no log file required):

   -- Top 10 queries by total time (MySQL 8.0 with performance_schema enabled)
   SELECT
       DIGEST_TEXT,
       COUNT_STAR AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 3) AS total_s,
       ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_s,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       ROUND(SUM_ROWS_EXAMINED / NULLIF(SUM_ROWS_SENT, 0), 1) AS rows_examined_ratio
   FROM performance_schema.events_statements_summary_by_digest
   ORDER BY total_s DESC
   LIMIT 10;
   

   Reset statistics after tuning to measure improvement:

   TRUNCATE performance_schema.events_statements_summary_by_digest;
   

Acting on Findings

8. Run EXPLAIN on the worst queries:

   EXPLAIN SELECT * FROM orders WHERE customer_email = 'alice@example.com'
   ORDER BY created_at DESC LIMIT 1;
   

   Look for:

   • type: ALL — full table scan
   • Extra: Using filesort — cannot use an index for ORDER BY
   • Extra: Using temporary — required a temp table for GROUP BY or DISTINCT
   • rows — estimated rows examined; compare to actual Rows_examined in the log

   Use EXPLAIN FORMAT=JSON for a detailed cost breakdown:

   EXPLAIN FORMAT=JSON SELECT ...;
   

9. Add missing indexes:

   -- For a query filtering on customer_email and ordering by created_at
   ALTER TABLE orders ADD INDEX idx_email_created (customer_email, created_at);
   
   -- Then verify the index is used
   EXPLAIN SELECT * FROM orders WHERE customer_email = 'alice@example.com'
   ORDER BY created_at DESC LIMIT 1;
   

10. Review log_queries_not_using_indexes output carefully. Small tables that are always faster with a full scan will appear here even though they are not a problem. Use min_examined_row_limit = 1000 to suppress trivial cases.

11. Rotate and manage the slow log file to prevent unbounded growth:

    -- In MySQL 8.0, flush to rotate
    FLUSH SLOW LOGS;
    

    Configure logrotate:

    /var/log/mysql/slow.log {
        daily
        rotate 7
        missingok
        notifempty
        delaycompress
        compress
        postrotate
            /usr/bin/mysqladmin flush-logs
        endscript
    }
    

Additional Information

• The slow query log captures wall-clock time, not CPU time. A query that is fast on CPU but waits 3 seconds for a row lock will appear in the log. Use Lock_time to distinguish lock waits from actual query slowness.
• performance_schema.events_statements_summary_by_digest aggregates by normalized fingerprint automatically and does not require disk I/O for each query. It is the preferred approach on servers where disk is a bottleneck.
• For Amazon RDS and Aurora, the slow query log can be published to CloudWatch Logs, enabling alerting and full-text search without SSH access to the server.
• long_query_time supports sub-second values: SET GLOBAL long_query_time = 0.5 logs queries > 500 ms.
• The Rows_examined value in the slow log corresponds to the Handler_read_* status variables. A high ratio of rows examined to rows sent is the single best indicator of a missing index.

Frequently Asked Questions

Q: Will enabling the slow query log slow down my database? A: Minimally. Each slow query write appends a few hundred bytes to a log file. The overhead is proportional to the number of slow queries — if you have many, the writes add up, but those queries are already hurting performance. Writing to a file on the same disk as data is an anti-pattern on busy servers; use a separate volume or pipe to a remote logging service.

Q: long_query_time = 0 logs everything. Is that safe? A: Safe but extremely noisy. On a busy server with 10,000 QPS, you will produce gigabytes of log per hour. Use performance_schema aggregation instead for full-spectrum visibility without log overhead.

Q: My slow queries have Lock_time of 3 seconds but Query_time of 3.001 seconds. Which one should I fix? A: The lock wait. The query itself is fast (0.001 s execution); the bottleneck is another transaction holding a lock. Investigate the blocker using sys.innodb_lock_waits or information_schema.INNODB_TRX.

Q: How do I find slow queries on Amazon RDS without server access? A: Enable the slow query log parameter (slow_query_log = 1) and set log_output = FILE in the RDS parameter group. Then use the AWS Console or CLI to download the log from the RDS console, or publish logs to CloudWatch Logs for real-time querying.

Q: pt-query-digest shows a query called administrator command: Connect. What is that? A: That represents the time for connection establishment, not a real query. If it appears frequently and high in the ranking, your application is opening and closing connections constantly — use a connection pool.