When a site gets slow, the database is often the reason — usually a handful of queries without the right index. The slow query log records every query that takes longer than a threshold, so you can stop guessing and fix the ones that matter.

Enable it at runtime

No restart needed:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;                        -- seconds
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

long_query_time applies to new connections; existing ones keep the old value until they reconnect.

Enable it permanently

In a config file (Ubuntu MySQL: /etc/mysql/mysql.conf.d/, MariaDB: /etc/mysql/mariadb.conf.d/):

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0
min_examined_row_limit = 100

Restart MySQL. The directory must be writable by the mysql user.

  • long_query_time — start with 1 second; lower to 0.2–0.5 once the worst offenders are fixed. 0 logs everything (useful briefly for a full picture, but the log grows fast).
  • log_queries_not_using_indexes — logs queries doing full scans even if fast; noisy on small tables. Combine with min_examined_row_limit, or enable briefly.

Reading the log

An entry looks like:

# Time: 2026-11-04T09:12:31.123456Z
# User@Host: shop[shop] @ localhost []
# Query_time: 3.412  Lock_time: 0.000 Rows_sent: 20  Rows_examined: 1254388
SET timestamp=1793440351;
SELECT * FROM orders WHERE customer_email = '[email protected]' ORDER BY created_at DESC LIMIT 20;

Rows_examined far larger than Rows_sent is the classic sign of a missing index: MySQL scanned 1.25 million rows to return 20.

Summarise with mysqldumpslow

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log     # top 10 by total time
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log     # most frequent

It groups similar queries (replacing literal values) so you see patterns, not individual executions.

Better: pt-query-digest

From Percona Toolkit:

sudo apt install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/slow.log > slow-report.txt

The report ranks query fingerprints by total time, with counts, average and 95th-percentile times, rows examined and an example query. Fix from the top: a query that takes 50 ms but runs 100,000 times a day matters more than one that takes 5 s once a week.

Fix what you find

For each top query:

  1. Run it with EXPLAIN (or EXPLAIN ANALYZE on MySQL 8.0.18+) — see MySQL EXPLAIN.
  2. Look for type: ALL (full table scan), Using filesort, Using temporary, large rows estimates.
  3. Add an index matching the WHERE and ORDER BY:
ALTER TABLE orders ADD INDEX idx_email_created (customer_email, created_at);
  1. Re-run and compare. See database indexing basics.

Other common fixes: avoid SELECT * on wide tables, paginate with keyset pagination instead of huge OFFSETs, cache results in Redis, move reports to a replica.

WordPress and other CMSs

Typical slow queries come from plugins (unindexed meta queries on wp_postmeta, huge autoloaded options, search). The Query Monitor plugin shows slow queries per page; the slow log shows them across all traffic. See WordPress database optimization.

Keep the log under control

Rotate it with logrotate (Debian/Ubuntu’s MySQL package includes a config for /var/log/mysql/*.log), and turn long_query_time back up after an investigation if the log grows too fast. See logrotate.

Alternatives and complements

  • performance_schema / sys schema: SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10; gives similar insight without log files (MySQL 5.7+).
  • Monitoring tools (PMM, Netdata, Prometheus mysqld_exporter) for trends over time.

Related: MySQL performance tuning and Nginx 504 Gateway Timeout, which slow queries often cause.

  • MySQL
  • MariaDB
  • Performance