Using the MySQL slow query log to fix slow WordPress sites

When WordPress feels slow, the database is a common suspect. The slow query log tells you which queries are actually expensive instead of guessing from plugin names. It is the difference between “the site is slow” and “the wp_postmeta table has a query reading 50,000 rows on every product page.”

Enable the slow query log

For a live investigation (no restart needed)

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

Persistent configuration

In /etc/mysql/mariadb.conf.d/50-server.cnf or /etc/mysql/my.cnf:

[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
log_slow_admin_statements = 1

Restart:

systemctl restart mariadb

Find the log path

SHOW VARIABLES LIKE 'slow_query_log_file';

Disable when done

SET GLOBAL log_queries_not_using_indexes = 'OFF';
SET GLOBAL slow_query_log = 'OFF';

Leaving log_queries_not_using_indexes on permanently can fill disks on busy sites.

Choose the right threshold

long_query_time	Use case
2 seconds	High threshold — catch only the worst offenders
1 second	Standard for most sites
0.5 seconds	Aggressive — useful for catching borderline queries
0 (log everything)	Do not use on production — fills disks instantly

Start at 1 second. If the log is still noisy, raise to 2. If you need more detail, lower to 0.5.

Capture real traffic

The slow query log is only useful if it captures actual workload patterns. Let the site run during normal usage for at least 15–30 minutes, covering:

• Homepage loads
• Product/category page browsing
• Site search
• Admin dashboard view
• Post editor (Gutenberg generates many queries)
• WooCommerce checkout (if applicable)
• Scheduled cron jobs (backup plugins, cache preloading)

Do not judge from one page load on a quiet staging site. If the site is only slow during peak hours, capture the log during peak hours.

Analyse the log

Using Percona Toolkit (recommended)

Install and run pt-query-digest:

apt install percona-toolkit
pt-query-digest /var/log/mysql/mysql-slow.log

The output shows:

• Query fingerprints (normalised SQL with placeholders)
• Execution count, total time, min/max/avg time
• Percentage of total load
• Example queries with execution plans

Manual analysis (without Percona Toolkit)

# Most recent entries
tail -200 /var/log/mysql/mysql-slow.log

# Count queries by table
grep -oP 'FROM\s+\w+' /var/log/mysql/mysql-slow.log | sort | uniq -c | sort -rn | head -20

Key things to look for

• Repeated identical queries — cache them with Redis
• Large Rows_examined — 50,000 rows examined for a 10-row result means missing indexes or bad queries
• Queries hitting wp_postmeta — WooCommerce and page builders generate endless meta queries
• LIKE '%term%' with leading wildcard — cannot use indexes, scans entire table
• Unindexed JOIN conditions — each join without an index multiplies execution cost
• Autoloaded options queries — SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' should return quickly; if it does not, autoload is bloated

WordPress-specific patterns

Postmeta bloat

wp_postmeta can become enormous on WooCommerce, membership, and builder-heavy sites. Common culprits:

-- Find rows per meta key
SELECT meta_key, COUNT(*) as count,
  ROUND(AVG(LENGTH(meta_value))) as avg_len,
  ROUND(SUM(LENGTH(meta_value))/1024/1024, 2) as total_mb
FROM wp_postmeta
GROUP BY meta_key
ORDER BY total_mb DESC
LIMIT 20;

Meta keys with millions of rows and large values are slowing every page that queries them.

Autoloaded options

Check the size of autoloaded data:

wp db query "SELECT COUNT(*) as entries, ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS autoload_mb FROM wp_options WHERE autoload='yes';"

If autoload_mb exceeds 1 MB, every uncached request pays that cost. Common bloat sources:

• Transients that should have expired but did not
• Plugin settings storing large JSON blobs
• Old plugin data that was never cleaned up

Clean up:

wp transient delete --expired
wp db query "DELETE FROM wp_options WHERE option_name LIKE '_transient_%' AND option_name NOT LIKE '_transient_timeout_%' AND LENGTH(option_value) < 20;"

Search queries

Default WordPress search uses LIKE with leading wildcards on wp_posts. For sites with more than 10,000 posts, this is slow. Solutions:

• Use Relevanssi or SearchWP with their own index tables
• Use Algolia or Elasticsearch for large databases
• At minimum, add a MySQL FULLTEXT index on wp_posts.post_title and wp_posts.post_content

Cron-related queries

WP-Cron events are stored in wp_options. Sites with thousands of overdue cron events create slow option queries. Check:

wp cron event list --status=due | wc -l

Fixes that usually help

Problem	Fix
Repeated identical queries	Redis object cache
Postmeta scans	Add Redis, audit meta keys
Autoloaded options bloat	Clean transients, audit autoloaded data
Missing indexes	Add targeted indexes (test on staging)
Plugin-generated garbage queries	Replace the plugin
Full table scans on large tables	Page cache for public traffic
Slow search	Replace WordPress search with dedicated index

Do not blindly add indexes

Indexes speed reads but slow writes, increase storage, and consume buffer pool memory. On WordPress:

1. Schema changes can break plugin assumptions
2. Adding an index to wp_postmeta.meta_value is tempting but may not help — the column type is longtext
3. WooCommerce already has indexes on its custom tables
4. Always test on staging and keep rollback SQL

-- Test index impact
EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = '_sku' AND meta_value = 'ABC-123';

-- Add index if EXPLAIN shows a full scan with no good reason
CREATE INDEX idx_meta_key_value ON wp_postmeta(meta_key, meta_value(191));

Monitoring over time

If the site has ongoing performance issues:

1. Enable slow query log during peak hours
2. Run pt-query-digest weekly
3. Compare reports to spot regressions
4. Track the top 5 slowest query patterns
5. Set up alerts for queries exceeding 5 seconds

A site that was fast six months ago and is slow today probably has data growth hitting query limits. The slow query log makes that visible instead of mysterious.