Insights / Databases
MySQL slow query log for business applications: find the queries that are actually slow
Slow pages are usually slow queries, and guessing which one wastes weeks. Turn on MySQL's slow query log, read what it tells you, and fix the queries costing your users the most time.
Kiran Bandarupalli · 17 Nov 2026 · 5 min read

Most slow business applications are not slow everywhere. They are slow in a handful of places, and the slowness almost always traces back to a handful of database queries. The page that takes six seconds to load is usually waiting on one query that takes five of them. The problem is that nobody can see which queries those are from the outside, so teams guess — and guessing is how you end up adding a cache to a page whose real problem is a missing index.
The MySQL slow query log is the cheapest way to stop guessing. It is built in, costs almost nothing to run when configured properly, and tells you exactly which queries are slow, how often they run, and how much work each one does.
What the slow query log actually records
A query lands in the slow log when its execution time exceeds long_query_time. The default of 10 seconds is nearly useless: a query that takes three seconds will ruin a page and never appear in the log. Start at 1 second, and drop to 0.5 once the obvious problems are fixed. You can also log queries that do not use an index, which catches fast-looking queries doing far more work than they should.
Each entry records more than the time. It records lock time, how many rows the query examined, and how many rows it sent back. That last pair is the single most useful ratio in the whole log. A query that examines 200,000 rows to return 20 has an index or filtering problem, and it will get worse every month as the table grows. One that examines 20 rows and returns 20, but still takes two seconds, is a different problem: lock contention or a query that is fine alone and terrible under load.
Turn it on without slowing production
Enable the log in the server configuration, not with a one-off command you will forget. Put it in the MySQL configuration file so it survives a restart:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000The last setting matters: without min_examined_row_limit, logging index-less queries floods the file with tiny lookups. On a busy server, start with log_queries_not_using_indexes off for a day, then enable it once you know the volume.
The cost is small because logging happens after the query has already run. The real risk is disk: on a server that has been slow for a long time, the log can grow fast, so point it at a partition with room, rotate it with logrotate, and check its size for the first few days. A slow query also holds a database connection while it runs, so a few bad queries can starve the pool — the reason MySQL connection pooling alone never fixes a slow application.
Reading the log: the numbers that matter
The raw log is chronological, which makes it nearly useless at volume. Aggregate it. The two tools worth knowing are mysqldumpslow, which ships with MySQL, and Percona's pt-query-digest, which produces a ranked report:
# Top 10 query patterns by total time
pt-query-digest /var/log/mysql/mysql-slow.log --limit 10
# Quick built-in alternative
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.logRank by total time, not by the single slowest query: a query taking 0.8 seconds and running 40,000 times a day costs your users far more than a 9-second query that runs twice. Then look at three numbers per pattern: rows examined versus rows sent, average lock time, and call count. High lock time points at long transactions holding rows hostage; a high call count for one query shape usually means the application is asking the same question in a loop. Before fixing anything, re-run the worst offenders with EXPLAIN: a query only slow under load is a concurrency problem, and an index will not help it.
From a slow query to a fix
Most slow queries fall into a short list of patterns, and each one has a standard fix:
- Rows examined far above rows sent. A missing or wrong index. Add the index the filter actually needs, and check whether a composite index covering the filter and the sort does the whole job.
- Fast alone, slow under load. Lock contention. Shorten the transactions around the query, and stop holding locks while you call an external service.
- The same query shape, thousands of times. Usually an N+1 pattern in the application. Fetch the related rows in one query instead of one per row.
- ORDER BY with LIMIT and a filesort. The sort has no index to use. A composite index in the sort order turns it into a walk down the index.
- A full scan that used to be fine. The table outgrew the query. This is the most common one in a growing business: it worked at 10,000 rows and breaks at 2 million.
Fix one query at a time and measure again: adding three indexes at once tells you nothing about which change helped, and every extra index taxes every write to that table. Index changes on a large production table deserve the same care as any schema change — the approach in zero-downtime database migrations applies here too — and make sure your backups that actually restore are current before you touch a production schema.
Make it a weekly habit, not a one-off
The slow query log pays off as a routine, not a rescue. Leave it on permanently, and review the top 10 query patterns once a week — after a release, and after any noticeable traffic change. Track one number: total slow-query time per day. When it trends up, something new is getting slower, and the log will name it before your users do.
Keep that number tied to your performance budgets: if a page has a time budget, the queries behind it have one too. If the slow work happens in a queue — nightly reports, invoice runs, imports — treat it with the same discipline as any other background job, and keep it visible in the same observability for asynchronous workflows you use for the rest of the system.
Checklist before you call it done
- Slow query log enabled in the configuration file, with a threshold of 1 second or lower.
- Log file on a partition with room, rotated regularly, and checked for growth in the first week.
- Queries ranked by total time using pt-query-digest or mysqldumpslow.
- Each top query checked with EXPLAIN before any fix is applied.
- One fix at a time, measured after each change, with no extra indexes left behind.
- Index or schema changes made with the zero-downtime approach, with backups verified first.
- Total slow-query time per day tracked and reviewed weekly, after every release.
Slow pages are usually slow queries, and slow queries are usually findable in ten minutes once the log is on. Turn it on, read the top of the list, fix the worst offender, and repeat. If the slow queries keep coming back after every release, that is exactly the kind of Cloud, VPS and DevOps work we do, and you can talk to us about getting it under control.