MySQL Slow Query Log: Finding the Query That Slows Your Site

When a site is slow, people usually blame the hosting or heavy images. On dynamic sites, though, one of the most common culprits is one or two bad database queries. A page may run dozens of queries, and if one of them takes several seconds, the whole page waits. MySQL's slow query log exists to catch exactly those queries, giving you evidence instead of guesses.

How the slow query log works

MySQL times every query. With the slow log enabled, any query exceeding long_query_time (in seconds, fractions allowed) is written to a file along with its duration, lock time, rows sent and rows examined. That last pair matters most: a query that returns 20 rows but examines a million is a clear sign of a missing index.

Enabling the log

On your own VPS or with admin rights, enable it at runtime (lost on restart):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

To make it permanent, add the same values to the [mysqld] section of the MySQL config (often under /etc/mysql/ on Debian and Ubuntu, or /etc/my.cnf elsewhere). The MySQL service must be able to write to the log directory:

[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 1
# also log queries without indexes (temporarily, grows fast):
# log_queries_not_using_indexes = 1

A very low threshold can fill the disk quickly. Start at one second, fix the worst offenders, then lower it to see the next layer.

Reading an entry

# Time: 2026-11-04T10:15:32.481920Z
# User@Host: shop_user[shop_user] @ localhost []
# Query_time: 3.214  Lock_time: 0.000120 Rows_sent: 24  Rows_examined: 1184302
SET timestamp=1793787332;
SELECT * FROM products WHERE category_id = 17 ORDER BY created_at DESC LIMIT 24;

Over three seconds to return 24 rows after scanning more than a million: the first question is whether category_id and created_at are indexed. Usually they are not.

Summarizing a large log

mysqldumpslow, shipped with MySQL, groups similar queries and sorts them. This shows the top ten by total time:

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

For deeper analysis, pt-query-digest from Percona Toolkit shows each query group's share of total time, so you can see which fix pays off most. Often one or two queries account for most of the load.

Checking with EXPLAIN

EXPLAIN SELECT * FROM products
WHERE category_id = 17
ORDER BY created_at DESC LIMIT 24;

type = ALL means a full table scan, an empty key means no index is used, and Using filesort means a separate sort step. A composite index on the filter and sort columns usually fixes it; re-run EXPLAIN afterwards and confirm the query leaves the log.

On shared hosting

Server-level log settings affect every customer, so regular users cannot change them. Application-level tools help instead: the Query Monitor plugin for WordPress shows per-query timings on each page, and frameworks like Laravel have their own query logs and debug bars. For analysis of a specific page, ask via the sayt.uz cabinet or support.

Example: a furniture store catalog

A furniture store in Namangan, example.uz, had a fast home page but category pages took five to six seconds. Before changing plans, the owner enabled the slow log for a day. mysqldumpslow showed nearly all the delay came from one query like the example above. A two-column index cut it from three seconds to a few milliseconds, category pages loaded in under a second, and no plan change was needed. The owner now keeps the log on and reviews the top queries weekly.