How to Check Slow Queries in MySQL
Identify slow queries and improve database performance using MySQL logging.
Before you start
Step-by-step instructions
1. Enable slow query log
Enable slow query logging in MySQL configuration.
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log2. Set slow query threshold
Define what counts as a slow query.
long_query_time = 23. Check slow query log
Read the slow query log file.
cat /var/log/mysql/slow.log4. Analyze slow queries
Use built-in tools to analyze slow queries.
mysqldumpslow /var/log/mysql/slow.logCommon issues
Check your MySQL configuration file for the correct log path.
Increase traffic or lower long_query_time to capture more queries.
Ensure MySQL has write permission to the log directory.
Final check
Monitoring slow queries regularly helps keep your database fast and stable. Optimize queries and indexes for better performance.
About this guide
This guide explains how to enable and check the MySQL slow query log to identify performance issues in your database.
How to follow this guide
- Enable slow query logging in MySQL.
- Set the long_query_time value.
- Check the slow query log file.
- Analyze queries and optimize them.
Why use this method?
Slow queries can significantly impact application performance. Identifying and optimizing them is essential for a stable system.
Frequently Asked Questions
What is a slow query?
A slow query is a SQL query that takes longer than a defined threshold to execute.
Where is the slow query log stored?
It depends on MySQL configuration, usually defined in my.cnf or my.ini.
What is long_query_time?
It defines the minimum execution time before a query is logged as slow.
Can I analyze slow queries automatically?
Yes. Tools like mysqldumpslow or pt-query-digest can help analyze logs.