A slow query log is a database log that records SQL statements whose execution time exceeds a configured threshold, together with details such as rows examined, lock time and the client that sent them. In database performance work, it is the primary evidence for deciding which queries to optimize first.
The term comes from MySQL and MariaDB, where the feature carries this name, but the same idea exists in PostgreSQL as duration logging and in other systems as query statistics views. Whatever the implementation, the log turns vague complaints about a slow database into a concrete list of statements with measurable cost.
How the slow query log captures statements
After each statement finishes, the server compares its execution time with the configured threshold. If the statement took longer, the server writes an entry to a file or a table containing the SQL text and execution statistics. Because the check happens at completion, a query that is still running does not appear until it ends.
Each entry contains several fields that together describe where the time went. The most useful ones are listed below in the form used by MySQL and MariaDB.
| Field | Meaning | What it reveals |
|---|---|---|
| Query_time | Total execution time in seconds | Overall cost of the statement |
| Lock_time | Time spent waiting for locks | Contention rather than slow execution |
| Rows_sent | Rows returned to the client | Size of the result |
| Rows_examined | Rows read to produce the result | Missing or unused indexes |
| Timestamp and user@host | When and from where it ran | Which application or job is responsible |
The ratio of Rows_examined to Rows_sent is often the fastest diagnostic. A query that reads a million rows to return ten is almost always missing a suitable index or filtering on an expression the index cannot use.
Configuration parameters and sensible values
The log is controlled by a small set of variables that can usually be changed at runtime without a restart. The right threshold depends on the workload: an interactive web application cares about tens of milliseconds, while a reporting system may tolerate several seconds.
| Setting | System | Purpose | Common value |
|---|---|---|---|
| slow_query_log | MySQL, MariaDB | Turns the log on or off | ON in production |
| long_query_time | MySQL, MariaDB | Threshold in seconds, fractions allowed | 0.1-2 |
| log_queries_not_using_indexes | MySQL, MariaDB | Logs full scans regardless of time | ON briefly during audits |
| min_examined_row_limit | MySQL, MariaDB | Skips queries reading few rows | 100-1,000 |
| log_min_duration_statement | PostgreSQL | Threshold in milliseconds | 200-1,000 |
| auto_explain.log_min_duration | PostgreSQL | Also logs the execution plan | Equal to or above main threshold |
Setting the threshold to zero logs every statement, which is useful for short sampling sessions but produces large files and measurable overhead on busy servers. Log rotation must be configured before any low threshold is left in place.
Analyzing the log effectively
Raw entries are rarely read one by one. Analysis tools normalize statements into fingerprints by replacing literal values with placeholders, so thousands of executions of the same query collapse into one line with total and average time.
Ranking by total time consumed usually matters more than ranking by the slowest single execution. A query taking 50 milliseconds but running a million times per day costs far more than a nightly report that takes two minutes once. Tools such as mysqldumpslow, pt-query-digest and pgBadger produce this kind of ranking.
| Tool | Data source | Strength | Limitation |
|---|---|---|---|
| Slow query log | Completed statements over a threshold | Full SQL text with real values | Misses fast but frequent queries |
| Performance Schema digests | In-memory statement statistics | Aggregates every query cheaply | Resets on restart |
| pg_stat_statements | PostgreSQL extension | Totals per normalized query | No individual examples |
| EXPLAIN ANALYZE | One query run on demand | Exact execution plan | Requires knowing the query first |
From log entry to fix
The log points at a problem; query optimization solves it. Typical patterns in the log map to a small set of remedies, summarized below.
| Pattern in the log | Likely cause | Typical fix |
|---|---|---|
| High Rows_examined, few rows sent | Missing index | Add a selective or composite index |
| High Lock_time | Blocking transactions | Shorten transactions, review locking |
| Same query thousands of times | Loop issuing one query per row | Batch into a single set-based query |
| Large OFFSET values | Deep pagination | Switch to keyset pagination |
| Spikes at fixed times | Scheduled jobs | Move jobs off-peak or to a replica |
Limitations and pitfalls
- The log can contain sensitive literal values such as email addresses, so access to it must be restricted.
- A slow query may be a victim rather than a cause, delayed by locks or by an overloaded disk.
- Logging to a table instead of a file adds overhead and can itself become contended.
- Queries slightly below the threshold stay invisible even when they dominate total load.
- Thresholds are global by default, so one setting rarely suits both interactive and batch workloads.
A plain-language summary
A slow query log works like a notebook where the database writes down every request that took too long. The administrator decides what too long means, for example half a second.
Later, someone reads the notebook to see which requests appear most often and which waste the most time. Instead of guessing why a website feels sluggish, the team gets a list of exact culprits. Fixing the top few entries usually brings the biggest improvement.
An example from practice
An e-commerce site becomes slow every weekday morning. The database administrator enables the slow query log on the MariaDB server with long_query_time set to 0.2 seconds and collects one day of entries, then runs a digest tool over the file.
The report shows that one product search query accounts for about 40% of all logged time. Each execution examines around 200,000 rows to return 20 results, because the filter combines category and price while only the category is indexed. Adding a composite index on category and price reduces rows examined to a few hundred. The query drops out of the slow log entirely, and morning page load times return to normal.