Find slow queries in MySQL with the slow query log

A slow query is usually the reason a web app feels sluggish while the server looks idle. MySQL can write every query that runs longer than a threshold to a log file, and that file tells you exactly which statements to fix. In this guide you will enable the slow query log, read it with mysqldumpslow and pt-query-digest, and use EXPLAIN to make the worst offenders fast.

Before you start

You need:

• A Linux VPS with MySQL 8.0/8.4 or MariaDB 10.x installed and running. If you have not installed it yet, see How to install MySQL 8.4 on Ubuntu and Debian.
• SSH access as a sudo user, and a MySQL account with enough privileges to change global variables (SUPER or SYSTEM_VARIABLES_ADMIN).
• The path to your server config file. On Ubuntu and Debian it is usually /etc/mysql/mysql.conf.d/mysqld.cnf; on AlmaLinux and Rocky Linux it is under /etc/my.cnf.d/. See Where is my.cnf? if you are unsure.
• Some free disk space. The slow log grows, and on a busy server it can grow quickly.

Enable the slow query log

Start at runtime so you can test without restarting MySQL. Connect as an admin user:

mysql -u root -p

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

long_query_time = 1 logs anything slower than one second. Values below 1 are allowed and are useful when you are hunting for smaller problems.

Open the config file and add a block under [mysqld]:

[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 = 100

min_examined_row_limit skips queries that only touch a few rows, which keeps noise out of the log.

Restart the service and confirm the file exists:

sudo systemctl restart mysql
sudo ls -l /var/log/mysql/mysql-slow.log

On MariaDB the service is usually mariadb instead of mysql. On AlmaLinux and Rocky the log may live under /var/lib/mysql/ depending on your config — check the value of slow_query_log_file.

Read the log with mysqldumpslow

mysqldumpslow ships with MySQL and MariaDB. It groups identical queries and prints the slowest groups first.

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log
sudo mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log

The flags matter:

• -s t sorts by total time, which shows what costs the server most overall.
• -s c sorts by how often the query runs.
• -s at sorts by average time per run.
• -t 10 prints only the top 10.

Add -g pattern to filter, for example -g 'orders'. The raw log itself looks like this:

# Time: 2026-01-01T10:14:22.481203Z
# User@Host: app[app] @ localhost []  Id: 8123
# Query_time: 4.812331  Lock_time: 0.000142  Rows_sent: 50  Rows_examined: 1843200
SET timestamp=1767262462;
SELECT id, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 50;

Rows_examined far above Rows_sent is the classic sign of a missing index.

Analyse with pt-query-digest

pt-query-digest from Percona Toolkit gives a much better report than mysqldumpslow: it ranks queries by total response time and shows percentiles.

sudo apt install percona-toolkit

sudo dnf install percona-toolkit

Then run it against the log:

sudo pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow-report.txt
less /tmp/slow-report.txt

The report starts with a profile of the whole log, then lists queries by rank. For each one you get the count, total time, average time, and a sample of the statement. Work from the top down — the first two or three entries usually account for most of the load.

Fix a slow query with EXPLAIN

Take the top query from the report and ask MySQL how it plans to run it:

EXPLAIN SELECT id, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 50;

Read four columns:

• type — ALL means a full table scan. ref, range or eq_ref are better.
• key — NULL means no index was used.
• rows — the estimated number of rows scanned. Smaller is better.
• Extra — Using filesort or Using temporary often means the query needs a different index.

A composite index that matches the filter and the sort usually fixes this:

CREATE INDEX idx_users_status_created ON users (status, created_at);

On MySQL 8.0.18 and later you can see real timings instead of estimates:

EXPLAIN ANALYZE SELECT id, email FROM users WHERE status = 'active' ORDER BY created_at DESC LIMIT 50;

Run EXPLAIN again after adding the index and confirm type improved and rows dropped.

Check it works

1. Confirm the log is on: SHOW VARIABLES LIKE 'slow_query_log'; should return ON.
2. Run a deliberately slow query, such as a large SELECT SLEEP(2); or a full scan on a big table.
3. Watch the file: sudo tail -f /var/log/mysql/mysql-slow.log. The query should appear within a second or two.
4. Re-run pt-query-digest after your index change and check that the query's average time and rank have dropped.

Troubleshooting

• The log file stays empty — long_query_time is too high for your workload, or the log is off. Lower it to 0.5 temporarily and confirm with SHOW VARIABLES.
• The log grows by gigabytes per hour — log_queries_not_using_indexes is on and the server is busy. Turn it off and rely on long_query_time alone.
• MySQL will not start after the config change — a typo in mysqld.cnf. Check sudo journalctl -u mysql -n 50 and fix the line.
• Permission denied writing the log — the mysql user cannot write to the directory you chose. Check ownership with ls -ld, or point slow_query_log_file at /var/lib/mysql/.
• pt-query-digest reports "Cannot open file" — you need sudo, or copy the log to a path your user can read.

Next steps

• Where is my.cnf? MySQL and MariaDB config files explained
• MySQL command-line cheat sheet for server admins
• How to check database and table sizes in MySQL and MariaDB
• Install and secure MariaDB or MySQL, and allow remote access safely

If the queries are already tuned and the server is still the bottleneck, it may be time for more CPU or RAM.