WordPress database bloat: how to clean and optimise your MySQL database

A 3-year-old WordPress database can balloon to 2+ GB with revisions nobody reads, transients that never expire, and WooCommerce log tables nobody empties. Here is how to identify bloat and clean it safely — with a backup first.

What causes WordPress database bloat?

Post revisions — WordPress saves a copy every time you edit a post. A single post edited 50 times creates 50 revision rows. Sites with hundreds of posts accumulate tens of thousands of revisions.

Auto-drafts — Every unsaved post creates an auto-draft. Old ones stack up in wp_posts.

Transients — WordPress plugins store temporary data as transients in wp_options. Expired transients that are never cleaned sit there indefinitely.

Spam comments — Akismet and other spam filters catch spam but leave it in the database.

Orphaned metadata — When posts or users are deleted, their metadata in wp_postmeta and wp_usermeta often stays.

WooCommerce logs and session data — wp_wc_sessions and wp_actionscheduler_logs grow fast on active stores.

Step 1: Check database size before cleaning

In phpMyAdmin, select your database and the "Operations" tab shows total size. Alternatively, run this SQL:

SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES 
WHERE table_schema = 'your_database_name'
ORDER BY (data_length + index_length) DESC;

This shows which tables are largest — usually wp_posts, wp_postmeta, and wp_options.

Step 2: Back up before touching anything

# Via WP-CLI
wp db export backup-before-cleanup.sql

# Via mysqldump
mysqldump -u dbuser -p dbname > backup-before-cleanup.sql

Never clean a database without a recent backup you've verified is complete.

Step 3: Delete post revisions

Via WP-CLI (recommended):

# Count revisions first
wp post list --post_type=revision --format=count

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

Via phpMyAdmin (SQL tab):

DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);

Prevent future buildup — add to wp-config.php:

define('WP_POST_REVISIONS', 3); // Keep only 3 revisions per post

Step 4: Clean expired transients

# WP-CLI
wp transient delete --expired

# Or delete all transients (they regenerate on next request)
wp transient delete --all

Via SQL:

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

Step 5: Remove spam and trash comments

wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force

Step 6: Clean auto-drafts and orphaned posts

-- Remove auto-drafts
DELETE FROM wp_posts WHERE post_status = 'auto-draft';

-- Remove orphaned post metadata
DELETE pm FROM wp_postmeta pm 
LEFT JOIN wp_posts wp ON wp.ID = pm.post_id 
WHERE wp.ID IS NULL;

Step 7: WooCommerce-specific cleanup

WooCommerce generates substantial data. On active stores:

# Clean old sessions (older than 30 days)
wp wc clean --type=sessions

# Delete old logs
wp wc clean --type=logs

For the action scheduler tables (wp_actionscheduler_actions, wp_actionscheduler_logs):

-- Delete completed actions older than 30 days
DELETE FROM wp_actionscheduler_actions 
WHERE status = 'complete' 
AND scheduled_date_gmt < DATE_SUB(NOW(), INTERVAL 30 DAY);

Step 8: Optimise tables after cleanup

After deleting rows, MySQL tables have gaps (fragmentation). Optimising reclaims the space:

# WP-CLI
wp db optimize

# SQL
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments;

Step 9: Set up automatic maintenance

Install WP-Optimize or Advanced Database Cleaner plugin to schedule:

• Weekly revision cleanup (keep last 3 per post)
• Monthly transient cleanup
• Monthly spam comment deletion

Automate with WP-CLI in a cron job:

# Add to crontab (runs every Sunday at 2am)
0 2 * * 0 cd /path/to/wordpress && wp transient delete --expired && wp comment delete $(wp comment list --status=spam --format=ids) --force

Results you can expect

A typical 3-year-old WordPress site with WooCommerce sees:

• Database size: 2 GB → 300 MB after cleanup
• Admin page load: 20–30% faster (fewer wp_options rows to scan)
• Query time: Noticeable improvement on sites with 10,000+ post revisions

Combine database optimisation with Redis object caching for the biggest admin-area speed improvement.