A·R
EN SR
WordPress · · 7 min read

WordPress Database Cleanup: A Practical Guide

Specific SQL queries and WP-CLI commands to clean transients, post revisions, orphaned metadata, and autoload bloat from WordPress databases with measurable performance improvements.

WordPress Database Cleanup: A Practical Guide

Transient Cleanup SQL Queries

Transients are WordPress’s built-in caching mechanism. When an external object cache is not configured, they are stored in wp_options as two rows per transient — one for the value, one for the expiration timestamp. Expired transients are supposed to be cleaned up automatically, but this cleanup is unreliable on busy sites and does not run at all if object caching is active. The result is thousands or millions of orphaned rows.

First, check the scale of the problem:

SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '_transient_%';
SELECT COUNT(*) FROM wp_options WHERE option_name LIKE '_transient_timeout_%' AND option_value < UNIX_TIMESTAMP();

Delete expired transients directly with SQL:

-- Delete expired transient timeout records
DELETE FROM wp_options 
WHERE option_name LIKE '_transient_timeout_%' 
AND option_value < UNIX_TIMESTAMP();

-- Delete transient values that no longer have a corresponding timeout record
DELETE FROM wp_options 
WHERE option_name LIKE '_transient_%' 
AND option_name NOT LIKE '_transient_timeout_%' 
AND option_id NOT IN (
    SELECT SUBSTRING(option_name, 20) 
    FROM wp_options 
    WHERE option_name LIKE '_transient_timeout_%'
);

For site transients (used across multisite), the prefix is _site_transient_:

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

DELETE FROM wp_options 
WHERE option_name LIKE '_site_transient_%' 
AND option_name NOT LIKE '_site_transient_timeout_%' 
AND option_id NOT IN (
    SELECT SUBSTRING(option_name, 25) 
    FROM wp_options 
    WHERE option_name LIKE '_site_transient_timeout_%'
);

After cleanup, add this to wp-config.php to limit future accumulation if you are not using an external object cache:

define('WP_CRON_LOCK_TIMEOUT', 60);

The real fix is installing Redis object caching. Once Redis is active, transients live in memory and never touch the options table. If you cannot install Redis, run the cleanup queries above weekly via cron.

Post Revision Limits

WordPress saves every edit as a revision. On a busy editorial site, the wp_posts table can balloon to 10x its necessary size. Limit revisions in wp-config.php:

/** Store a maximum of 5 revisions per post */
define('WP_POST_REVISIONS', 5);

/** Or disable revisions entirely on sites that do not need them */
// define('WP_POST_REVISIONS', false);

The WP_POST_REVISIONS constant only affects future revisions. Existing revisions remain in the database. Set this before launch — changing it on an existing site with 100,000 revisions requires the cleanup below.

Cleaning Existing Revisions with SQL

Before deleting, count what you are dealing with:

SELECT post_parent, COUNT(*) as revision_count 
FROM wp_posts 
WHERE post_type = 'revision' 
GROUP BY post_parent 
ORDER BY revision_count DESC 
LIMIT 20;

To delete all revisions except the most recent 3 per post:

-- Create a temporary table of revisions to keep
CREATE TEMPORARY TABLE revisions_to_keep AS
SELECT id FROM (
    SELECT ID as id,
           ROW_NUMBER() OVER (PARTITION BY post_parent ORDER BY post_modified DESC) as rn
    FROM wp_posts
    WHERE post_type = 'revision'
) ranked WHERE rn <= 3;

-- Delete all revisions not in the keep list
DELETE FROM wp_posts 
WHERE post_type = 'revision' 
AND ID NOT IN (SELECT id FROM revisions_to_keep);

-- Clean up the temporary table
DROP TEMPORARY TABLE revisions_to_keep;

For MySQL versions that do not support window functions, use this approach instead:

-- Simple approach: delete all revisions older than 90 days
DELETE FROM wp_posts 
WHERE post_type = 'revision' 
AND post_date < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Then optimize the table
OPTIMIZE TABLE wp_posts;

Always back up the database before running mass deletion queries. The OPTIMIZE TABLE call reclaims disk space after large deletions — on InnoDB, this is equivalent to ALTER TABLE ... ENGINE=InnoDB and may take significant time on large tables.

Spam Comment Deletion

-- Count spam comments
SELECT COUNT(*) FROM wp_comments WHERE comment_approved = 'spam';

-- Delete all spam comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';

-- Delete orphaned comment meta
DELETE FROM wp_commentmeta 
WHERE comment_id NOT IN (SELECT comment_ID FROM wp_comments);

-- Optimize both tables
OPTIMIZE TABLE wp_comments;
OPTIMIZE TABLE wp_commentmeta;

Akismet and other anti-spam plugins mark comments as spam but rarely delete them. A site running for three years with moderate traffic can accumulate 50,000+ spam comments. Each spam comment has metadata rows in wp_commentmeta. Delete both the comments and their metadata, then optimize the tables to reclaim space.

Orphaned Postmeta and Usermeta Cleanup

When posts or users are deleted, their metadata often remains behind. Plugins that store post meta sometimes fail to clean up when posts are removed:

-- Find orphaned postmeta (meta for posts that no longer exist)
SELECT COUNT(*) FROM wp_postmeta pm 
LEFT JOIN wp_posts p ON pm.post_id = p.ID 
WHERE p.ID IS NULL;

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

-- Find orphaned usermeta (meta for users that no longer exist)
SELECT COUNT(*) FROM wp_usermeta um 
LEFT JOIN wp_users u ON um.user_id = u.ID 
WHERE u.ID IS NULL;

-- Delete orphaned usermeta
DELETE um FROM wp_usermeta um 
LEFT JOIN wp_users u ON um.user_id = u.ID 
WHERE u.ID IS NULL;

The DELETE ... LEFT JOIN ... WHERE ... IS NULL pattern is the correct approach for MySQL. It handles the join and deletion in a single query. On a site with heavy plugin use, orphaned meta rows can number in the hundreds of thousands.

wp_options Autoload Audit

Every row in wp_options with autoload = 'yes' is loaded into memory on every single WordPress request. A bloated autoload set adds 50-200ms to every page load. Identify the worst offenders:

-- Find the largest autoloaded values
SELECT option_name, LENGTH(option_value) as size_bytes 
FROM wp_options 
WHERE autoload = 'yes' 
ORDER BY size_bytes DESC 
LIMIT 30;

Look for plugin names you recognize in the option_name column. Common bloat sources:

  • woocommerce_* options from WooCommerce — some are unnecessarily autoloaded
  • jetpack_* options from Jetpack
  • Analytics plugin caches that store full reports in options
  • Page builder CSS caches stored as autoloaded strings
  • Old plugin options from deactivated plugins

For options that do not need to load on every request, disable autoloading:

UPDATE wp_options SET autoload = 'no' 
WHERE option_name IN ('some_large_option', 'another_cache_option');

Only disable autoload for options that are accessed conditionally — if an option is needed on every page load, keep it autoloaded. The LENGTH() function output is in bytes. Anything over 10,000 bytes (10KB) in autoload is suspicious and should be investigated.

Term Relationships Cleanup

The wp_term_relationships table connects posts to terms. When posts are deleted, their term relationships should be removed, but this does not always happen:

-- Find term relationships for deleted posts
SELECT COUNT(*) FROM wp_term_relationships tr 
LEFT JOIN wp_posts p ON tr.object_id = p.ID 
WHERE p.ID IS NULL;

-- Delete orphaned term relationships
DELETE tr FROM wp_term_relationships tr 
LEFT JOIN wp_posts p ON tr.object_id = p.ID 
WHERE p.ID IS NULL;

-- Find terms with no relationships (orphaned terms)
SELECT t.term_id, t.name 
FROM wp_terms t 
LEFT JOIN wp_term_relationships tr ON t.term_id = tr.term_taxonomy_id 
WHERE tr.term_taxonomy_id IS NULL;

-- Delete orphaned terms and their taxonomy entries
DELETE tt FROM wp_term_taxonomy tt 
LEFT JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id 
WHERE tr.term_taxonomy_id IS NULL;

DELETE t FROM wp_terms t 
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id 
WHERE tt.term_id IS NULL;

Orphaned term relationships are common on sites that import and delete content in bulk. The relationship rows accumulate and slow down taxonomy queries, especially on sites with heavy category or tag usage.

Weekly Optimization Routine

Automate this routine and run it weekly. Add it as a shell script at /usr/local/bin/wp-db-cleanup.sh:

#!/bin/bash
SITE_URL="https://example.com"
LOG_FILE="/var/log/wp-db-cleanup.log"

echo "$(date): Starting cleanup" >> $LOG_FILE

# Clean expired transients
wp transient delete --expired --path=/var/www/html >> $LOG_FILE 2>&1

# Optimize database tables
wp db optimize --path=/var/www/html >> $LOG_FILE 2>&1

# Delete spam comments
wp comment delete $(wp comment list --status=spam --format=ids --path=/var/www/html) --force --path=/var/www/html >> $LOG_FILE 2>&1

# Delete trashed comments
wp comment delete $(wp comment list --status=trash --format=ids --path=/var/www/html) --force --path=/var/www/html >> $LOG_FILE 2>&1

echo "$(date): Cleanup complete" >> $LOG_FILE

Make it executable and add to cron:

chmod +x /usr/local/bin/wp-db-cleanup.sh

# Add to crontab for weekly Sunday 3 AM execution
0 3 * * 0 /usr/local/bin/wp-db-cleanup.sh

WP-CLI Commands for Database Maintenance

WP-CLI provides several built-in commands for database optimization. Install WP-CLI if you have not already:

curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
chmod +x wp-cli.phar
sudo mv wp-cli.phar /usr/local/bin/wp

Core database commands:

# Optimize all tables (runs OPTIMIZE TABLE on each)
wp db optimize

# Repair tables (useful after crashes)
wp db repair

# Check table status
wp db check

# Export the database
wp db export backup-$(date +%Y%m%d).sql

# Run an arbitrary SQL query
wp db query "SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision'"

# Delete all expired transients
wp transient delete --expired

# List all transients
wp transient list --format=table

# Delete a specific transient
wp transient delete my_transient_name

# Search and replace in the database (useful for domain migrations)
wp search-replace 'old-domain.com' 'new-domain.com' --dry-run
wp search-replace 'old-domain.com' 'new-domain.com'

The --dry-run flag on search-replace shows what would change without executing. Always run dry-run first. The wp transient delete --expired command handles the timeout record lookup and deletion safely, and should be preferred over manual SQL when possible.

Monitoring Table Sizes

Add this query to your monitoring toolkit. Run it monthly to track growth:

SELECT 
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC;

Track these sizes over time. If a table grows by more than 50% month-over-month without corresponding traffic growth, you have a runaway plugin or logging mechanism. The most common culprits are:

  • wp_options growing from unchecked transient accumulation
  • wp_postmeta growing from plugins storing logs or analytics data
  • wp_commentmeta growing from spam plugins that store metadata

Using wpdb for Custom Queries

When writing cleanup plugins or admin tools, use WordPress's $wpdb class instead of raw PHP database connections:

global $wpdb;

// Safe query with prepare()
$old_revisions = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT ID FROM {$wpdb->posts} 
         WHERE post_type = 'revision' 
         AND post_date < %s",
        date('Y-m-d', strtotime('-90 days'))
    )
);

// Bulk delete with proper table prefix handling
$deleted = $wpdb->query(
    $wpdb->prepare(
        "DELETE FROM {$wpdb->posts} 
         WHERE post_type = 'revision' 
         AND post_date < %s 
         LIMIT %d",
        date('Y-m-d', strtotime('-90 days')),
        1000
    )
);

echo "Deleted {$deleted} revision rows.";

Always use $wpdb->prepare() with user input or dynamic values. The LIMIT clause on bulk deletions prevents table locks on busy sites. For deletions affecting more than 10,000 rows, chunk them into batches of 1,000 and sleep between batches to avoid replication lag on replicated setups.

Plugin Alternatives with Pros and Cons

Several plugins handle database cleanup if you prefer not to run SQL manually:

  • WP-Optimize: Handles revisions, transients, spam, and table optimization from a GUI. Free version is sufficient for most sites. Adds an admin menu item you may not want on client sites.
  • Advanced Database Cleaner: More granular control over what gets cleaned. Good for identifying orphaned items. The pro version handles multisite cleanup.
  • WP-Sweep: Uses WordPress's built-in deletion functions instead of raw SQL, which triggers all hooks and keeps data integrity intact. Slower than raw SQL but safer. Best for sites with complex inter-plugin dependencies.

For sites under active development, write your own cleanup routines using the SQL patterns above. Plugins add overhead — their admin interfaces, scheduled events, and option storage create their own database bloat. Custom cleanup code in an mu-plugin gives you full control without the overhead.

After implementing these cleanup procedures, measure the results with Query Monitor. A clean database should execute the front-page query set in under 10ms total query time. If your database queries still exceed 50ms after cleanup, the problem is not bloat — it is missing indexes or expensive meta queries that need application-level fixes.

Written by

admin

View all posts

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *