MySQL slow query log: turn it on, read it

The slow query log records every statement that takes longer than a time you choose. It is off by default. You need the privilege to set global variables, or access to your host's parameter settings on a managed service.

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- seconds
SHOW VARIABLES LIKE 'slow_query_log_file';

A new long_query_time applies only to connections opened afterwards. Existing connections, such as an application's connection pool, keep the old value until they reconnect. Changes made with SET GLOBAL are lost on restart. To keep them, put the same settings in the [mysqld] section of the configuration file, or in the parameter group on a managed service.

# Time: 2026-10-18T09:14:03.512345Z
# User@Host: app[app] @ 10.0.0.5 []
# Query_time: 4.210000  Lock_time: 0.000100  Rows_sent: 20  Rows_examined: 498120
SELECT id, total FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

The sample is made up for this guide.

• Query_time is how long it took, in seconds.
• Rows_examined against Rows_sent is the telling pair. Here MySQL read about 498,000 rows to return 20, which points to a missing index.
• Lock_time is time spent waiting for table or metadata locks. A query blocked on another transaction's InnoDB row lock mostly shows up in Query_time instead, so a long Query_time with little Lock_time and few rows examined can still mean a wait.

One slow run matters less than a medium-slow query that runs thousands of times. Group the log by query shape and sort by total time. MySQL ships mysqldumpslow for this:

mysqldumpslow -s t -t 10 /path/from/slow_query_log_file

-s t sorts by total time and -t 10 shows the top ten. Percona Toolkit's pt-query-digest does the same with more detail. Use the path that SHOW VARIABLES LIKE 'slow_query_log_file' printed. Some managed services write the log to the mysql.slow_log table by default (log_output is TABLE), so there is no file to read until you set log_output to FILE or query the table.