WordPress Database Optimization

WordPress databases bloat with revisions, transients, spam, orphaned metadata, and sessions. Run cleanup monthly on active sites.

Identify Bloat

-- Table sizes
SELECT table_name,
       ROUND(data_length/1024/1024, 2) AS 'Data MB',
       ROUND(index_length/1024/1024, 2) AS 'Index MB'
FROM information_schema.tables
WHERE table_schema = 'your_database_name'
ORDER BY data_length DESC;

-- Count post revisions
SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

-- Count orphaned postmeta (no matching post)
SELECT COUNT(*) FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

-- Count expired transients
SELECT COUNT(*) FROM wp_options
WHERE option_name LIKE '_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();

Cleanup SQL (phpMyAdmin)

-- Delete all post revisions
DELETE FROM wp_posts WHERE post_type = 'revision';

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

-- Delete expired transients
DELETE FROM wp_options
WHERE option_name LIKE '_transient_%'
AND option_name LIKE '%_transient_timeout_%'
AND option_value < UNIX_TIMESTAMP();

-- Delete spam and trash comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';
DELETE FROM wp_commentmeta
WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

-- Optimize tables (defragments, updates statistics)
OPTIMIZE TABLE wp_options, wp_posts, wp_postmeta, wp_comments;

WP-CLI Database Cleanup

# Delete all revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force

# Delete expired transients
wp transient delete --all --expired

# Optimize all database tables
wp db optimize

# Check for table errors
wp db check

# Full database size report
wp db size

Prevent Future Bloat

// Limit post revisions (in wp-config.php)
define('WP_POST_REVISIONS', 3);

// Or per post type (in functions.php)
add_filter('wp_revisions_to_keep', function($num, $post) {
    if (in_array($post->post_type, ['product', 'service'])) return 3;
    return $num;
}, 10, 2);

// Increase autosave interval (default 60s — reduce revision count)
define('AUTOSAVE_INTERVAL', 300); // 5 minutes

Automated Cleanup with WP-Optimize

1. Install WP-Optimize
2. Settings > Scheduler: Run weekly
3. Enable: Remove post revisions, expired transients, spam comments, orphaned data
4. Run optimization after cleanup (defragments tables)
5. Schedule: Monday at 3 AM (after MainWP update pipeline)