How to Analyze Slow Query Log in MySQL for Better Performance

To analyze the slow query log in MySQL, first enable it by setting slow_query_log=ON and specify a log file. Then, use tools like mysqldumpslow or pt-query-digest to summarize and understand which queries are slow and need optimization.

Syntax

The slow query log is controlled by several MySQL system variables:

• slow_query_log: Enables or disables the slow query log.
• slow_query_log_file: Specifies the file path where slow queries are logged.
• long_query_time: Sets the minimum execution time (in seconds) for a query to be logged.
• log_queries_not_using_indexes: Logs queries that do not use indexes.

These can be set dynamically or in the MySQL configuration file.

sql

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

Example

This example shows how to enable the slow query log, run some queries, and then analyze the log using mysqldumpslow.

bash

# Enable slow query log
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/tmp/mysql-slow.log';
SET GLOBAL long_query_time = 1;

# Run some queries that might be slow
SELECT SLEEP(2);
SELECT * FROM large_table WHERE non_indexed_column = 'value';

# Analyze slow query log using mysqldumpslow
!mysqldumpslow /tmp/mysql-slow.log

Output

Reading mysql slow query log from /tmp/mysql-slow.log
Count: 1  Time=2.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), SELECT SLEEP(2)
Count: 1  Time=1.50s (1.5s)  Lock=0.00s (0s)  Rows=100 (100), SELECT * FROM large_table WHERE non_indexed_column = 'value'

Common Pitfalls

• Not enabling the slow query log before running queries means no data is collected.
• Setting long_query_time too high may miss important slow queries.
• Forgetting to check file permissions can prevent MySQL from writing the log.
• Analyzing raw log files manually is hard; use tools like mysqldumpslow or pt-query-digest for summaries.

sql

/* Wrong: long_query_time too high, missing slow queries */
SET GLOBAL long_query_time = 10;

/* Right: lower threshold to catch more slow queries */
SET GLOBAL long_query_time = 1;

Quick Reference

Setting	Description	Example Value
slow_query_log	Enable or disable slow query logging	ON
slow_query_log_file	File path for slow query log	/var/log/mysql/mysql-slow.log
long_query_time	Minimum query time in seconds to log	1
log_queries_not_using_indexes	Log queries not using indexes	ON

Key Takeaways

Enable the slow query log and set a reasonable long_query_time to capture slow queries.

Use tools like mysqldumpslow or pt-query-digest to analyze and summarize slow query logs.

Check file permissions and MySQL settings to ensure logs are properly written.

Lower long_query_time to catch more slow queries but avoid excessive logging.

Regularly review slow query logs to optimize database performance.