Slow Query Log and EXPLAIN for MySQL on a VPS

Guessing which query is slow is how you add the wrong index. The slow query log is the list of statements that already crossed a time threshold on this server, with this data, under this load. EXPLAIN is how you read the plan for one of those statements. Together they turn "the API feels slow" into a table, a type, a key, and a rows estimate you can change.

On a single VPS the bottleneck is usually one of three things: a full table scan that used to be fine at 10k rows, a sort that spills to disk, or a query that runs thousands of times per minute and is only a little slow. The log catches all three. Your intuition catches the first and misses the third.

Turn the slow log on with a threshold you will actually read

long_query_time = 1 is a starting point, not a religion. If the log is empty, lower it. If it is a flood, raise it or filter by examined rows. Log to a file, not to a table, on a production VPS; table logs write more work into the same instance you are trying to observe. Keep the file on High-Speed SSD and rotate it.

# /etc/mysql/mariadb.conf.d/93-slow.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 1
log_queries_not_using_indexes = 0
min_examined_row_limit = 100

log_queries_not_using_indexes looks useful and is noisy. A tiny lookup against a three-row status table will show up forever. Start with time plus min_examined_row_limit, then turn the index flag on for a short window when you are hunting.

Apply without a restart if you want a same-day look:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;
SET GLOBAL min_examined_row_limit = 100;
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Let it run through a busy hour. Then summarize. pt-query-digest from Percona Toolkit is the usual aggregator. mysqldumpslow is already on many images and is enough to find the top offenders by count and by time.

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

-s t sorts by total time, -s c by count. A query that takes 80 ms and runs 40,000 times an hour often beats a 2 second report that runs twice a day. Fix the one the log says is expensive, not the one a developer remembers.

Read EXPLAIN as a short table, not as a mystery

Take the normalized statement from the slow log, fill in representative literals, and run EXPLAIN. On MariaDB 10.1+ you can also EXPLAIN FORMAT=JSON for extra fields. Start with the tabular form.

EXPLAIN SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2026-05-01'
  AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

The columns that matter:

• type: const and ref are good. range can be fine. ALL is a full scan of that table. index is a full scan of an index, which is still a scan.
• key: the index actually used. NULL with type ALL is the red flag.
• rows: estimate of rows examined. It can be wrong, but an estimate of 4 million on a 50-row LIMIT is a plan you do not want.
• Extra: Using filesort and Using temporary are costs. Using index is a covering index, which is a win. Using where is normal.

Add an index that matches the WHERE and JOIN, not an index per column. For the query above, (status, created_at) on orders is the usual shape, with user_id already being a primary or unique key on users. Create indexes one at a time, re-run EXPLAIN, then re-check the slow log after a few hours. Unused indexes slow writes.

If EXPLAIN looks perfect and the query is still slow, the problem is elsewhere: lock waits, a cold buffer pool, or a network round trip from the app. SHOW ENGINE INNODB STATUS and the process list will say so. Do not add indexes to treat a lock.

Keep the log on after the first index

Keep the slow log on after you fix the first query. The next schema change will grow a table, and the next deploy will add a missing WHERE. The log is cheaper than a profiler you remember to attach once a quarter. Rotate the file, and glance at mysqldumpslow after every release.

The slow log names the statement, EXPLAIN names the plan, and one matching index is usually the patch. Leave the log on. When you want a quiet box to capture a real workload copy, spin up Ubuntu on Netbay in Lucknow in under 60 seconds and replay production dumps against it at netbayhosts.in.