WordPress stores almost everything in its database: posts, pages, options, user data, transients, session data, and metadata. Over time, this grows, fragments, and accumulates cruft. Here is how to diagnose and fix database-related slowdowns. Once the schema and queries are clean, a Redis object cache keeps the database from repeating the same work on every request.

Start with a health check

Use WP-CLI to get an overview:

wp db size --tables
wp eval 'var_dump(wp_get_db()->dbh);'

Then check MySQL connection time and query latency:

mysql -e "SHOW PROCESSLIST;" wp_local
mysql -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';"

The most common WordPress database problems

1. Expired transients filling the options table

Transients are cached data stored in wp_options. They have expiry times, but WordPress does not always clean them up automatically, especially on older versions.

Find transients that should have expired:

SELECT * FROM wp_options
WHERE option_name LIKE '%_transient%'
AND option_value < UNIX_TIMESTAMP(NOW());

Clean them up:

wp transient delete --expired
wp eval 'delete_expired_transients(true);'

Or with SQL directly:

DELETE FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP(NOW());

2. Orphaned post metadata

When a post is deleted, its metadata is supposed to be deleted with it. Plugin errors can leave orphaned rows.

Find orphaned metadata:

SELECT COUNT(*) FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

Clean up:

DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

3. Orphaned comment metadata

Same problem, different table:

DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON c.comment_ID = cm.comment_id
WHERE c.comment_ID IS NULL;

4. Revision spam in wp_posts

WordPress saves revisions every time you edit a post. For sites with many pages and frequent edits, this can double or triple the rows in wp_posts.

Find the revision count:

SELECT post_type, COUNT(*) as revisions
FROM wp_posts
WHERE post_status = 'inherit'
GROUP BY post_type;

Limit revisions in wp-config.php:

define('WP_POST_REVISIONS', 3);
define('AUTOSAVE_INTERVAL', 120);

Delete existing revisions:

wp post delete $(wp post list --post_type=revision --format=ids) --force

Or SQL:

DELETE FROM wp_posts WHERE post_type = 'revision';

5. Large wp_options with autoloaded data

WordPress loads all options with autoload=yes on every page load. Some plugins add large values (caches, big arrays) that should not be autoloaded.

Check autoloaded data size:

SELECT SUM(LENGTH(option_value)) as autoload_size
FROM wp_options
WHERE autoload = 'yes';

Find the largest autoloaded options:

SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

MySQL configuration for WordPress

The default MySQL configuration is conservative. For a VPS with 2–4 GB RAM running WordPress, tune these in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
# InnoDB buffer pool — should be ~70% of available RAM
innodb_buffer_pool_size = 1G

# Log file size — should be ~25% of buffer pool
innodb_log_file_size = 256M

# Flush writes more aggressively (good for write-heavy sites)
innodb_flush_log_at_trx_commit = 2

# Connection limit
max_connections = 100

# Query cache is removed in MySQL 8, skip it

After changing MySQL config:

sudo systemctl restart mysql

Using EXPLAIN to find slow queries

-- Find queries using no index
EXPLAIN SELECT * FROM wp_posts WHERE post_date < '2025-01-01';

-- Find queries with full table scans
SHOW STATUS LIKE 'Handler_read%';

Common missing indexes on WordPress:

-- Add index to wp_postmeta if missing
ALTER TABLE wp_postmeta ADD INDEX idx_post_id (post_id);
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key (meta_key);

-- Add index to wp_options
ALTER TABLE wp_options ADD INDEX idx_autoload (autoload);

-- Add index to wp_comments
ALTER TABLE wp_comments ADD INDEX idx_comment_post_id (comment_post_id);
ALTER TABLE wp_comments ADD INDEX idx_comment_approved (comment_approved);

Object caching with Redis

Moving frequently-used database queries into Redis dramatically reduces MySQL load:

# Install Redis
sudo apt install redis-server
sudo systemctl enable --now redis-server

# Configure Redis for WordPress object cache
# Use the Redis Object Cache plugin (installs the object-cache.php drop-in):
# https://github.com/rhubarbgroup/redis-cache

Verify Redis is running and connected:

redis-cli ping
# Should return: PONG

When to move to a dedicated database server

Signs that the database has outgrown the web server:

  • MySQL CPU usage consistently above 50% even with caching enabled
  • SHOW PROCESSLIST regularly shows 50+ concurrent connections
  • Queries taking more than 100ms to complete (not 10ms)
  • The web server and database server are competing for the same RAM

Moving to a dedicated database server (or managed MySQL like DigitalOcean Managed Databases or AWS RDS) is straightforward:

  1. Set up the new database server
  2. Replicate from the old server (or do a point-in-time dump and import)
  3. Update WP_DB_HOST in wp-config.php
  4. Test thoroughly
  5. Decommission the local MySQL
// In wp-config.php
define('DB_HOST', 'mysql://user:password@db-server:3306/wp_database');