Finding Slow MySQL Queries with the Slow Query Log

The site on Linux hosting has gotten slow, TTFB has gone from 200 milliseconds to 3 seconds, and you still don't know which query is doing it. If you only look at CPU and RAM, you'll never get an answer. The right tool is the slow query log; the very thing MySQL itself writes and most admins never turn on.

First, check whether the slow query log is even on

Before making any change to my.cnf, ask MySQL itself:

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

If slow_query_log is OFF, it means you've had no data until today and you have to start from scratch. The long_query_time value is also usually 10 seconds by default, which is practically useless for a WordPress site; no query takes 10 seconds, but a thousand 300-millisecond queries will bring the site down.

Enabling without a restart

To start right now without taking the service down:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'TABLE';

A value of 0.5 seconds is a good starting point. If traffic is high and the log is exploding, take it to 1. If the site is small and you're after details, 0.2 is also reasonable. With log_output = 'TABLE', the output lands in the mysql.slow_log table and can be queried with SQL. If you set FILE, it writes to a log file, which is better for large-scale analysis but more manual to read.

These settings are lost when MySQL restarts. To make them permanent, write this in the [mysqld] section of the config file:

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

That last line matters. Many people set log_queries_not_using_indexes to 1 because they think it means "catch every query without an index." In practice, on WordPress sites, it also logs small tables like wp_options with a few hundred rows, and the log file reaches gigabytes within hours. I leave it at 0 by default and only turn it on when I'm chasing a specific pattern.

Reading the output: which line matters

Every log entry has three things that are useful to you: the execution time, the number of rows MySQL examined, and the query itself. A real example:

# Time: 2024-05-11T09:14:22.318471Z
# User@Host: wpuser[wpuser] @ localhost []
# Query_time: 4.812301  Lock_time: 0.000142  Rows_sent: 12  Rows_examined: 184320
SET timestamp=1715418862;
SELECT * FROM wp_postmeta WHERE meta_key = '_thumbnail_id' ORDER BY meta_value LIMIT 12;

The number Rows_examined: 184320 versus Rows_sent: 12 tells the whole story. MySQL read one hundred and eighty thousand rows to return twelve. This ratio, not the execution time, is the main criterion. A query with a Query_time of half a second but a ratio of 15000 to 1 will bring the site down under concurrent load much faster than a two-second query with a ratio of 2 to 1.

If the output is in the table, run this sorting query:

SELECT sql_text, query_time, rows_examined, rows_sent
FROM mysql.slow_log
ORDER BY rows_examined DESC
LIMIT 20;

Sorting by rows_examined rather than query_time usually gets you to the root faster.

EXPLAIN: look before adding an index

Take the suspicious query and put EXPLAIN in front of it:

EXPLAIN SELECT * FROM wp_postmeta
WHERE meta_key = '_thumbnail_id'
ORDER BY meta_value LIMIT 12;

In the output, look at the type and key columns. If type is ALL, you have a full table scan. If key is empty, no index was used. The rows column estimates how many rows will be read, and if it doesn't match the actual Rows_examined, the table statistics are stale and you need ANALYZE TABLE wp_postmeta;.

The right index, not just any index

For the query above, a single-column index on meta_key isn't enough because we also have ORDER BY meta_value. A composite index will do the job:

ALTER TABLE wp_postmeta ADD INDEX idx_key_value (meta_key, meta_value(20));

The order of columns in a composite index matters. If you write it in reverse, MySQL can't use it for sorting and you get filesort again. That (20) is also a prefix; on a longtext column you can't create an index without a prefix and you'll get the error ERROR 1170 (42000): BLOB/TEXT column used in key specification without a key length.

This is where people go wrong: a missing index isn't always the cause

The most common mistake I see is this: the site admin opens the log, sees a query with high Rows_examined, immediately adds an index, and the site is still slow. The sign is also that EXPLAIN shows key populated after the index but the execution time hasn't changed.

Three real causes behind this situation:

• Locking on the table. If Lock_time in the log is high, the problem is lock contention, not read speed. A long transaction is holding everyone else in a queue.
• N+1 queries. A thousand 5-millisecond queries, each below the long_query_time threshold, never show up in the log but their total brings the site down. Here you need to temporarily set long_query_time to 0 or use performance_schema.
• Data transfer from disk. A query that does SELECT * and returns heavy columns, even with the right index, saturates disk IO. If you're on Linux hosting with a SATA disk and iostat shows %util above 90, the issue isn't the index.

Take the second point seriously. In a WordPress installation with 15 plugins, seeing 800 queries in a single page render is completely normal and none of them show up in the slow query log. If the log is empty but the site is slow, the problem is elsewhere; check WordPress wp-cron, because cron running concurrently with a user request is one of the most common causes of sudden database spikes.

The cost you have to accept

Turning on the slow query log isn't free. Every query has to be timed and written to I/O. Under heavy load, with log_output = 'FILE' and a slow disk, the log itself can eat 5 to 10 percent of throughput. That's why I never leave it permanently on on a high-traffic server. The pattern is: turn it on, collect data for 24 hours, analyze, turn it off. If needed, again next week.

For analysis, pt-query-digest from the Percona toolkit works much better than the human eye:

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

Its output sorts queries by total time consumed, not by individual executions. This is exactly what you want.

Frequently asked questions

Why is the slow query log empty but the site is slow?

Because long_query_time is probably too high or the problem isn't the query. Set the value to 0.2 and wait an hour. If it's still empty, the issue is at another layer: table locking, N+1 queries, or disk I/O saturation.

Does adding an index always make a query faster?

No. An index on columns with low cardinality (like a status column with three values) is almost useless and only increases write cost. Also, every extra index slows down INSERT and UPDATE. Measure the index with EXPLAIN before and after, not by guessing.

What's the difference between log_output TABLE and FILE?

In TABLE mode, the output lands in the mysql.slow_log table and can be filtered and sorted with SQL, but under heavy load it creates contention itself. In FILE mode, writing is lighter and it's more suitable for analysis with tools like pt-query-digest. For short-term troubleshooting, TABLE is easier; for a high-traffic server, FILE.

How do I tell whether a slow query is from WordPress itself or from a plugin?

Look at the table names in the query. The wp_options and wp_postmeta tables usually mean a plugin or theme. To be sure, deactivate plugins one by one and clear the log after each change to see which query disappears. If you're looking for ready-made tools to test speed and check server status, ServerNet's free tools are a good starting point.

If after these steps it turns out the bottleneck is the server itself and not the query, it's time to think about Linux hosting with more suitable resources. For WordPress sites, WordPress hosting with optimized MySQL settings usually causes less trouble.

Run these two lines right now: SET GLOBAL slow_query_log = 'ON'; and SET GLOBAL long_query_time = 0.5;. Open the log tomorrow morning and look at the Rows_examined column, not Query_time.