How to Find Slow Queries in MySQL Quickly and Easily

To find slow queries in MySQL, enable the slow_query_log and set a threshold with long_query_time. Then, check the slow query log file or use the performance_schema tables to analyze queries that take longer than the set time.

Syntax

Enable the slow query log and set the time threshold to log queries slower than that time.

• slow_query_log: Turns the slow query log on or off.
• slow_query_log_file: Specifies the file where slow queries are logged.
• long_query_time: Sets the minimum execution time (in seconds) for a query to be considered slow.

mysql

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

Example

This example enables the slow query log, sets the threshold to 1 second, and shows how to read the slow query log file.

mysql

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

-- After running some queries, check the slow query log file:
-- Use shell command to view the file (outside MySQL):
-- tail -n 20 /tmp/mysql-slow.log

Output

Sample output from slow query log file:
# Time: 2024-06-01T12:00:00.000000Z
# User@Host: user[user] @ localhost []
# Query_time: 1.234  Lock_time: 0.000 Rows_sent: 10  Rows_examined: 1000
SELECT * FROM large_table WHERE column = 'value';

Common Pitfalls

Common mistakes when finding slow queries include:

• Not enabling the slow query log before running queries.
• Setting long_query_time too high or too low, causing too many or too few queries to log.
• Forgetting to check file permissions on the slow query log file.
• Not using FLUSH LOGS after changing log file location.

Always restart MySQL or use SET GLOBAL commands carefully to apply changes.

mysql

/* Wrong: Not enabling slow query log */
SET GLOBAL long_query_time = 1;
-- No slow_query_log enabled, so no queries logged

/* Right: Enable slow query log */
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

Quick Reference

Setting	Description	Example Value
slow_query_log	Enable or disable slow query logging	ON or OFF
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 or 2
log_queries_not_using_indexes	Log queries that do not use indexes	ON or OFF

Key Takeaways

Enable the slow query log with SET GLOBAL slow_query_log = ON to start logging slow queries.

Set long_query_time to define what counts as a slow query based on execution time.

Check the slow query log file to review slow queries and optimize them.

Remember to verify file permissions and restart MySQL if needed after configuration changes.

Use performance_schema tables as an alternative to analyze slow queries without log files.