Every MySQL performance problem leaves a record. The slow query log is where that record lives — a straightforward, built-in tool that captures every query taking longer than a threshold you define.
If your database feels sluggish and you are not sure why, turning on the slow query log is almost always the fastest way to get a clear answer.
MySQL’s slow query log records queries that exceed your defined time threshold. It captures:
• How long the query took to execute
• How long it waited for locks
• How many rows MySQL examined to produce the result
• How many rows were actually returned
That last comparison — rows examined versus rows returned — is often the most revealing. A query that examines 500,000 rows to return 5 almost always has a missing index.
No restart required. Run these commands in MySQL:
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ‘ON’;
To keep these settings after a restart, add them to my.cnf :
Each entry includes a header with timing information, followed by the query itself.
MySQL includes mysqldumpslow , a command-line utility for summarizing slow query log data:
# Top 10 slowest queries by total execution time
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
This groups similar queries together and ranks them by the metric you choose — total time, call count, or rows examined. Focus on total time, not just the single slowest query. A query that takes 2 seconds but runs 5,000 times per day matters more than a 30-second query that runs once a week.
SQLyog’s Query Profiler gives you this same analysis through a GUI — run any SELECT query and switch to the Profiler tab for detailed execution metrics.
Once you have identified a slow query, run EXPLAIN on it to understand why it is slow:
EXPLAIN SELECT * FROM orders
WHERE status = ‘pending’ AND created_at > ‘2026-01-01’;
For the example above, adding a composite index on (status, created_at) allows MySQL to use an index range scan instead of reading every row.
The slow query log tells you what happened. Real-time monitoring tells you what is happening right now.
Use both: the slow query log to identify recurring problems and pattern trends, and real-time monitoring to catch new issues the moment they appear.
On a busy server, the slow query log can grow quickly. A few practices to keep it manageable:
• Start with a higher threshold (2–3 seconds) and lower it once you have addressed the worst offenders
• Rotate logs regularly using MySQL’s FLUSH SLOW LOGS command or your operating system’s log rotation utility
• Disable log_queries_not_using_indexes once your indexing strategy is solid — it generates a lot of noise on optimized databases
Want to catch slow queries before users do? Try MONyog free — real-time MySQL query monitoring with zero server overhead.
Start at 1–2 seconds to capture the most impactful slow queries first. Once you have fixed those, lower the threshold to 0.5 seconds, then 0.1 seconds to find more subtle issues. Setting it too low on a busy server generates an overwhelming log volume — work gradually.
The overhead is minimal — typically under 1% CPU impact even on busy servers. The risk of not knowing about slow queries far outweighs the small monitoring cost.
Rows_examined is how many rows MySQL had to read to produce your result. If a query returns 5 rows but examined 500,000, MySQL is doing 499,995 units of wasted work on every execution. Adding the right index dramatically reduces this number.
Sort by total execution time rather than worst individual case. A query taking 2 seconds that runs 10,000 times per day is far more important to fix than a 60-second query that runs once a week. Focus on cumulative impact.
Yes. MySQL logs statements executed within stored procedures and triggers if they exceed long_query_time . This is useful for identifying slow operations buried inside database-layer business logic.
The slow query log captures queries retrospectively after they execute slowly in production. SQLyog’s Query Profiler analyzes a specific query you run in your current session, showing execution stages and timing in a visual format. They complement each other — the slow log finds problems, the profiler helps you understand and fix them.
Yes, for most production environments. The overhead is minimal, and having the log running means you have immediate diagnostic data when something goes wrong. Many experienced DBAs treat a production MySQL instance without slow query logging as flying blind.
Setting long_query_time = 0 logs every single query — this is useful for brief diagnostic sessions but will generate enormous log volume on any active server and degrade performance noticeably. Use a practical threshold (1 second or higher) for ongoing production logging.
