The MySQL slow query log table is one of the most practical tools available to database administrators who need to identify and fix performance bottlenecks—yet it remains widely misunderstood.
Every DBA eventually faces the same challenge: a production system that felt perfectly snappy six months ago now grinds through certain operations at a pace that frustrates end users and strains server resources. The culprit is almost always a handful of problematic queries—queries that nobody caught during development because they ran against a small dataset, or queries that crept into the codebase through a well-intentioned but poorly thought-out schema change. MySQL's slow query log, and in particular its table-based logging mode, gives you a structured, queryable record of exactly those troublemakers.
This guide walks through the slow query log table in MySQL, explaining how it differs from the traditional flat-file log, where its limitations lie, and how experienced DBAs bridge those gaps with proper tooling. Whether you manage an on-premises MySQL 5.6+ server or a cloud-hosted instance such as Amazon RDS where filesystem access is restricted, understanding the
mysql.slow_log table is a core competency worth developing. You can also explore our deeper coverage of
MySQL performance tuning strategies and
query optimization techniques for broader context.
What Is the MySQL Slow Query Log?
Before diving into the mysql slow query log table specifically, it helps to understand what the slow query log does at a fundamental level. The slow query log is MySQL's built-in mechanism for recording statements that exceed a configurable execution-time threshold. When enabled, MySQL writes diagnostic information about each qualifying statement—including elapsed time, lock time, rows examined, and the full query text—so that DBAs can identify and tune the worst offenders.
MySQL supports two output destinations for this log: a flat text file on the server's filesystem and a structured table inside the
mysql system database. Both serve the same purpose, but they have meaningfully different characteristics that affect how you collect, query, and act on the data.
Enabling the MySQL Slow Query Log Table
Before the mysql slow query log table produces any useful data, you need to enable it and set an appropriate threshold. The following configuration block can be placed in your
my.cnf (or
my.ini on Windows) under the
[mysqld] section:
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_output = TABLE
Setting
log_output = TABLE directs slow query records to the
mysql.slow_log table rather than (or in addition to) the flat file. You can also combine both destinations with
log_output = FILE,TABLE if you want the redundancy. For dynamically adjusting these settings on a running server without a restart, use
SET GLOBAL:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'TABLE';
SET GLOBAL log_queries_not_using_indexes = 'ON';
Verify the current configuration with:
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_output';
The mysql.slow_log Table: Structure and Schema
When table-based logging is active, MySQL writes slow query records into
mysql.slow_log. Understanding the schema tells you exactly what data you have to work with:
DESCRIBE mysql.slow_log;
The result set looks like this:
+----------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------------------+-------+
| start_time | timestamp(6) | NO | | CURRENT_TIMESTAMP | |
| user_host | mediumtext | NO | | NULL | |
| query_time | time(6) | NO | | NULL | |
| lock_time | time(6) | NO | | NULL | |
| rows_sent | int(11) | NO | | NULL | |
| rows_examined | int(11) | NO | | NULL | |
| db | varchar(512) | NO | | NULL | |
| last_insert_id | int(11) | NO | | NULL | |
| insert_id | int(11) | NO | | NULL | |
| server_id | int(10) unsigned | NO | | NULL | |
| sql_text | mediumblob | NO | | NULL | |
| thread_id | bigint(21) unsigned | NO | | NULL | |
+----------------+---------------------+------+-----+---------------------+-------+
Key columns to pay attention to are
query_time and
lock_time, which store the actual and lock-waiting durations;
rows_examined, which indicates how many rows MySQL had to inspect to satisfy the query; and
sql_text, which holds the full statement text.
A Critical Limitation of the MySQL Slow Query Log Table: Integer Truncation
Here is something that trips up many DBAs who switch from file-based to table-based logging without reading the fine print. In the flat log file, MySQL writes
query_time and
lock_time with microsecond precision—as floating-point numbers like
0.003421. When writing to the table, however, the internal logging code casts these values to integer seconds before storage.
The practical consequence is severe: any query that completes in under one second is recorded as having a
query_time of zero.
If your
long_query_time is set to something like
0.5 seconds to catch moderately slow queries, all of those records end up with a stored duration of zero—making aggregate calculations like average query time completely misleading.
The official
MySQL documentation on the slow query log acknowledges both logging modes but does not prominently call out this precision difference, which is why it catches so many teams off guard.
Querying the MySQL Slow Query Log Table Effectively
Despite the time-precision limitation, the
mysql.slow_log table is still valuable for identifying query patterns, spotting schema-level problems, and auditing which users or applications are generating the heaviest workload. The following queries form a practical starting toolkit.
Finding the Most Expensive Queries by Execution Time
SELECT
sql_text,
COUNT(*) AS execution_count,
SEC_TO_TIME(SUM(TIME_TO_SEC(query_time))) AS total_query_time,
SEC_TO_TIME(AVG(TIME_TO_SEC(query_time))) AS avg_query_time,
MAX(rows_examined) AS max_rows_examined,
db
FROM mysql.slow_log
WHERE start_time >= NOW() - INTERVAL 24 HOUR
GROUP BY sql_text, db
ORDER BY total_query_time DESC
LIMIT 20;
Identifying Queries That Scan the Most Rows
SELECT
sql_text,
user_host,
rows_examined,
rows_sent,
ROUND(rows_sent / NULLIF(rows_examined, 0) * 100, 2) AS selectivity_pct,
start_time
FROM mysql.slow_log
ORDER BY rows_examined DESC
LIMIT 25;
The
selectivity_pct column is particularly telling. A query that examines 500,000 rows but only returns 10 has a selectivity percentage below 0.01%, which almost always points to a missing index or a query that is pulling far too broad a dataset before filtering.
Breaking Down Slow Queries by User and Application
SELECT
SUBSTRING_INDEX(user_host, '[', 1) AS user_name,
COUNT(*) AS query_count,
SEC_TO_TIME(SUM(TIME_TO_SEC(query_time))) AS total_time
FROM mysql.slow_log
WHERE start_time >= NOW() - INTERVAL 1 HOUR
GROUP BY user_name
ORDER BY total_time DESC;
Rotating and Truncating the slow_log Table
Unlike a flat log file that you can rotate with logrotate, the
mysql.slow_log table requires a different approach. The safer pattern is to disable logging momentarily, truncate, and re-enable:
SET GLOBAL slow_query_log = 'OFF';
TRUNCATE TABLE mysql.slow_log;
SET GLOBAL slow_query_log = 'ON';
Converting Table Output for Use with pt-query-digest
One of the most common workflows for MySQL performance engineers is feeding slow query data into
Percona Toolkit's pt-query-digest, which fingerprints queries, groups similar statements, and produces a ranked report showing where query time is concentrated. The challenge is that
pt-query-digest reads the flat-file log format—not directly from the
mysql.slow_log table.
The solution is to query the table and emit output in the format that
pt-query-digest expects. Below is a shell script that performs this conversion:
#!/bin/bash
# Exports mysql.slow_log to a flat file for pt-query-digest
OUTPUT_FILE="/tmp/slow_query_export.log"
mysql -uREADONLY_USER -pPASSWORD --skip-column-names --quick --silent --no-auto-rehash --compress mysql --execute "
SELECT CONCAT(
'# Time: ', DATE_FORMAT(start_time, '%y%m%d %H:%i:%s'), CHAR(10),
'# User@Host: ', user_host, CHAR(10),
'# Query_time: ', TIME_TO_SEC(query_time),
' Lock_time: ', TIME_TO_SEC(lock_time),
' Rows_sent: ', rows_sent,
' Rows_examined: ', rows_examined, CHAR(10),
'SET timestamp=', UNIX_TIMESTAMP(start_time), ';', CHAR(10),
sql_text, ';', CHAR(10)
)
FROM mysql.slow_log
ORDER BY start_time;
" > "${OUTPUT_FILE}"
echo "Export complete. Run: pt-query-digest ${OUTPUT_FILE}"
Once exported, run
pt-query-digest against the file:
pt-query-digest /tmp/slow_query_export.log --limit 20 --order-by Query_time:sum --report-format query_report,profile > /tmp/slow_query_report.txt
Using the Performance Schema as a Complement
MySQL's
Performance Schema offers a complementary view of query performance through the
events_statements_summary_by_digest table. Unlike the slow query log, the Performance Schema records aggregated statistics for every normalized query pattern—and it uses nanosecond-precision timers, so sub-second precision is never an issue.
SELECT
SCHEMA_NAME AS db,
DIGEST_TEXT AS query_pattern,
COUNT_STAR AS total_executions,
ROUND(SUM_TIMER_WAIT / 1e12, 4) AS total_latency_sec,
ROUND(AVG_TIMER_WAIT / 1e12, 6) AS avg_latency_sec,
SUM_ROWS_EXAMINED AS total_rows_examined,
SUM_NO_INDEX_USED + SUM_NO_GOOD_INDEX_USED AS no_index_count
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY total_latency_sec DESC
LIMIT 20;
The Performance Schema carries a non-trivial memory overhead and aggregates parameter values out of queries. The slow query log, by contrast, preserves the full literal SQL text—invaluable when you need to identify which specific parameter values caused a particular slow execution. For a thorough guide on integrating both tools, see our article on
leveraging the MySQL Performance Schema.
MySQL Slow Query Log Table on Amazon RDS and Cloud Environments
On managed MySQL environments like Amazon RDS, Google Cloud SQL, and Azure Database for MySQL, filesystem-based slow query log files are either inaccessible or retrievable only through cloud management consoles with a delay. Table-based logging is the practical alternative: the
mysql.slow_log table is accessible via standard SQL connections, so you can query it programmatically and integrate it with your existing monitoring stack without special permissions.
To verify slow query settings on Amazon RDS:
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'long_query_time';
Keep in mind that the integer-truncation limitation applies equally in RDS environments, reinforcing the need for Performance Schema supplementation when sub-second precision matters.
Practical Recommendations for the MySQL Slow Query Log Table in Production
- Set
long_query_time conservatively at first. Starting at 1 second makes sense for most workloads. Once you have addressed the obvious outliers, progressively lower the threshold to 0.5 and then 0.1 seconds to surface the next tier of problems.
- Enable
log_queries_not_using_indexes selectively. On a busy server, this flag generates enormous volume. Consider enabling it during scheduled analysis windows rather than continuously in production.
- Automate table truncation on a schedule. Without rotation,
mysql.slow_log grows indefinitely. A scheduled job that truncates after exporting retained data keeps the table manageable and prevents storage concerns.
- Correlate slow query data with
EXPLAIN output. The slow query log tells you that a query was slow; it does not tell you why. Always follow up with EXPLAIN or EXPLAIN ANALYZE on the offending statement. For a detailed walkthrough of reading EXPLAIN output, consult the official MySQL EXPLAIN documentation.
- Use
pt-query-digest for structured reporting. Running a weekly digest and comparing it to the previous week's output is one of the most effective early-warning systems for query performance regressions.
Conclusion
The MySQL slow query log table is an underappreciated yet powerful tool in the performance engineer's toolkit. It makes slow query data accessible via standard SQL, integrates naturally into cloud-managed environments, and enables programmatic analysis without requiring filesystem access. Its primary limitation—integer truncation of time values—is real and consequential, but it is manageable once you understand it and compensate with Performance Schema data or file-based log exports where precision matters.
Sustainable MySQL performance requires treating the slow query log as one instrument within a broader observability stack that also includes index analysis, buffer pool tuning, and replication health monitoring. If you are managing a complex MySQL environment and need expert guidance, the team at
MinervaDB specializes in exactly these challenges. Explore our
MySQL consulting services to learn how we can help your organization achieve consistent, reliable database performance.
