WordPress Slow Query Log — How to Read It and Act

The single most useful MySQL feature for WordPress

Most slow WordPress sites have 1-5 specific slow queries that account for 80%+ of the slowness. Find those queries, optimize them, and the site is fast. Everything else (caching, CDN, image optimization) helps but won't fix the underlying database performance issue.

The MySQL slow query log identifies exactly those 1-5 queries.

Enabling the slow query log

In MySQL:

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

Or persistent in /etc/mysql/my.cnf:

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

long_query_time = 1 logs queries taking 1+ second. Start with 1. Once you fix those, drop to 0.5, then 0.2.

Reading the slow log

A slow query log entry looks like:

# Time: 2026-05-13T10:32:15.123456Z
# User@Host: wpuser[wpuser] @ localhost []  Id: 12345
# Query_time: 3.245678  Lock_time: 0.000123 Rows_sent: 1  Rows_examined: 850000
SET timestamp=1715594535;
SELECT post_id FROM wp_postmeta WHERE meta_value LIKE '%search%';

Key fields: - Query_time: how long the query took - Lock_time: how long the query waited for locks - Rows_sent: rows actually returned - Rows_examined: rows MySQL had to read

Danger signal: high Rows_examined with low Rows_sent. Missing index.

Aggregating with mysqldumpslow

# Top 20 by total time
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# Top 20 by average time
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log

# Top 20 by count
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log

Output looks like:

Count: 5234  Time=2.45s (12823s)  Lock=0.001s (5.2s)  Rows=1.0 (5234)
  SELECT meta_value FROM wp_postmeta WHERE post_id = N AND meta_key = 'S'

Count: 5234 runs × 2.45s = 12,823s total. Fix this and you save 3.5 hours of MySQL time per log window.

Patterns and their meanings

Pattern A — postmeta scan without index

SELECT meta_value FROM wp_postmeta WHERE post_id = N AND meta_key = 'S'

With Rows_examined > 100, missing composite index. Add:

ALTER TABLE wp_postmeta ADD INDEX idx_post_meta_key (post_id, meta_key);

Pattern B — autoload options query

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

If slow (>500ms), autoload payload too big. Clean up.

Pattern C — large WP_Query with meta_query

SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts
INNER JOIN wp_postmeta ON ...
WHERE wp_postmeta.meta_key = '_price'

Multiple postmeta joins + SQL_CALC_FOUND_ROWS = catastrophic. Disable SQL_CALC_FOUND_ROWS or move to lookup table.

Pattern D — wp_users authentication

SELECT * FROM wp_users WHERE user_login = 'S' OR user_email = 'S'

If frequent and slow, bot brute-forcing. Block at WAF.

Pattern E — comments queries

SELECT * FROM wp_comments WHERE comment_status = 'hold'

With 100,000+ pending comments, slows admin. Delete spam:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

The full workflow

# 1. Enable slow log
mysql -e "SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;"

# 2. Browse site or wait for traffic for 30+ minutes

# 3. Disable so it doesn't grow forever
mysql -e "SET GLOBAL slow_query_log = 'OFF';"

# 4. Aggregate
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log

# 5. For each top 5, EXPLAIN
mysql -e "EXPLAIN <slow query>;"

# 6. Apply fix (index, refactor, cache)

# 7. Re-enable, re-test

When EXPLAIN is necessary

EXPLAIN SELECT meta_value FROM wp_postmeta WHERE post_id = 12345 AND meta_key = '_yoast_wpseo_metadesc';

Focus on: - type: ALL = full scan (bad); ref, eq_ref = index lookup (good) - key: which index used; NULL = no index - rows: estimated rows read - Extra: Using index good; Using filesort, Using temporary bad

Indexes have a cost

Each index: - Costs disk space (5-30% of table size) - Slows INSERT/UPDATE/DELETE - Takes time to build on existing data

Only add indexes you measure to help.

Common mistakes when reading slow logs

Trusting `long_query_time = 10` results — start at 1 second
Optimizing queries that run 5 times/day — focus on high-frequency
Adding indexes without `ALGORITHM=INPLACE` — locks table during creation
Ignoring lock_time — high lock_time means lock contention

When to call a specialist

Database optimization is highly leveraged. A 4-hour audit + fix can drop TTFB by 50-90%.

Speed audit including database deep-dive. For broader performance see speed recovery.