Get A Quote
Get A Quote
Get A Quote

How to Clean Up Your WordPress Database

Recent Posts

Over time, your WordPress database silently fills with data you no longer need — post revisions, expired transients, spam comments, plugin leftovers, abandoned sessions, and auto-drafts. None of it serves any purpose. All of it slows down your site.

A WordPress database that started at 5MB can grow to 500MB or more over a few years of normal operation — even on a modest blog. For WooCommerce stores and membership sites, multi-gigabyte databases are common.

Cleaning up that database reduces query execution time, improves page load speed, and makes your backups faster and smaller. This guide covers exactly what to clean, how to clean it safely, and how to prevent bloat from accumulating again.

What Causes WordPress Database Bloat?

Post revisions — every save creates a new copy
Auto-drafts — abandoned posts accumulate
Trashed posts — still stored until emptied
Spam comments — caught but not deleted
Expired transients — cached data never cleaned
Orphaned postmeta — metadata without a parent post
WooCommerce data — sessions, logs, webhooks
Plugin leftovers — tables and settings after deletion
User sessions — expired sessions never purged
Before cleaning: Back up your database. Database cleanup operations are irreversible — deleted rows are gone. Go to Settings → UpdraftPlus Backups → Backup Now (database only). Verify the backup is stored remotely before proceeding.
1
WP-Optimize Plugin
Free — Recommended
Recommended Free

WP-Optimize is the most comprehensive free WordPress database optimisation plugin. It cleans all common sources of database bloat through a straightforward interface.

Installation

Go to Plugins → Add New
Search WP-Optimize
Install and activate

Running Database Cleanup

Go to WP-Optimize → Database — you'll see a list of optimisable items with current counts and sizes. Work through each category:

  • Post Revisions
    Deletes all stored post revisions. For a large blog, this can remove thousands of rows.
  • Auto-Drafts
    Deletes auto-saved drafts. Safe — auto-drafts are temporary saves, not published content.
  • Trashed Posts
    Cleans posts and pages in the trash. Verify you don't need anything in the trash before running.
  • Spam Comments
    Removes all spam-marked comments. Safe — Akismet-caught spam has no legitimate purpose.
  • Trashed Comments
    Removes comments in the trash. Verify no legitimate comments are in there.
  • Expired Transients
    Often the largest cleanup item — hundreds of thousands of rows on some sites. Expired transients serve no purpose.
  • Orphaned Postmeta
    Removes metadata rows with no associated post. These are genuinely orphaned data.
  • Orphaned Usermeta
    Removes user metadata with no associated user account. Safe to clean.
  • Orphaned Term Relationships
    Removes tag/category associations for deleted posts. Safe to clean.
  • Duplicated Postmeta
    Removes duplicate metadata rows. Safe to clean.

Optimize Database Tables

After cleaning, click Run optimization for the Optimize database tables option. This defragments tables and rebuilds indices — similar to defragmenting a hard drive. Can be slow on large databases but improves query performance.

Schedule Regular Cleanups

In WP-Optimize → Settings, enable Enable scheduled cleanup
Set frequency: Monthly for most sites, Weekly for high-activity sites
Select which cleanups to run automatically
Best method for most users. Free, visual, scheduled, and covers all common bloat sources.
2
phpMyAdmin (Manual)
Full Control — Direct SQL

For sites where you want precise control, phpMyAdmin allows direct database manipulation.

Access phpMyAdmin

Log in to cPanel → Databases → phpMyAdmin → select your WordPress database.

Clean Post Revisions

SQL
DELETE FROM wp_posts WHERE post_type = 'revision';

To keep the most recent revision of each post:

SQL
DELETE FROM wp_posts 
WHERE post_type = 'revision' 
AND ID NOT IN (
    SELECT MAX(ID) 
    FROM wp_posts 
    WHERE post_type = 'revision' 
    GROUP BY post_parent
);

Clean Auto-Drafts

SQL
DELETE FROM wp_posts WHERE post_status = 'auto-draft';

Clean Expired Transients

SQL
DELETE FROM wp_options 
WHERE option_name LIKE '_transient_%' 
AND option_name NOT LIKE '_transient_timeout_%';

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

Clean Spam Comments

SQL
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (
    SELECT comment_ID FROM wp_comments
);

Optimize All Tables

In phpMyAdmin, select your database, check all tables, and from the "With selected" dropdown at the bottom, choose Optimize table.

Warning: Always verify your table prefix. Some installations use wp_ while others use a custom prefix. Running queries with the wrong prefix will fail silently or target wrong tables.
Maximum control for technical users. Always back up before running any SQL queries directly.
3
WP-CLI
For Command Line Users

If you have SSH access to your server, WP-CLI provides fast, scriptable database cleanup.

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

# Clean expired transients
wp transient delete --expired

# Optimize all database tables
wp db optimize
Tip: WP-CLI is the fastest method for large databases — much quicker than waiting for a web interface. It also avoids PHP timeout issues that can affect plugin-based cleanup on very large databases.
Fastest method for large databases. Requires SSH access and command line familiarity.

Preventing Database Bloat: Ongoing Best Practices

  • Limit post revisions. Add to wp-config.php to cap revisions at 5 per post:
    PHP
    define( 'WP_POST_REVISIONS', 5 );
  • Empty your trash regularly. Set WordPress to auto-empty trash after 7 days (default is 30):
    PHP
    define( 'EMPTY_TRASH_DAYS', 7 );
  • Use a caching plugin's transient management. Quality caching plugins like WP Rocket and LiteSpeed Cache actively manage expired transients rather than leaving them to accumulate.
  • Delete plugins cleanly. When removing a plugin, look for a "Delete all plugin data" option in its settings before deactivating. If no option exists, check for custom database tables via phpMyAdmin.
  • Run WP-Optimize monthly. Schedule it as part of your monthly maintenance routine — takes 5 minutes.

What NOT to Clean

Not everything in the database should be cleaned — some "bloat-looking" data is actually important:

Active WooCommerce orders — needed for records, accounting, disputes
User accounts — may have order history, memberships, access records
Core WordPress options in wp_options — critical site settings
Non-expired transients — active cached data WordPress is currently using

How Much Performance Improvement Can You Expect?

Small DB (<50MB)
Minimal impact
Moderate bloat (50–200MB)
Noticeable improvement
Significant bloat (200MB+)
20–40% faster queries
WooCommerce session data
30–50% size reduction
Measure before and after: In phpMyAdmin, the database size is shown next to the database name. Check before cleanup, run cleanup, then check again to see the actual improvement.

Frequently Asked Questions

Q: Is it safe to delete post revisions?
A: Generally yes — post revisions exist to help you recover from accidental edits, but they're not needed once the post is published and stable. The exception: if you regularly need to roll back recent edits, keep a few revisions. You can limit revisions to 3–5 per post rather than deleting all of them.
Q: Will cleaning the database delete any content from my site?
A: No — cleaning removes backend housekeeping data (revisions, spam, transients, orphaned metadata) not your actual published content. Your posts, pages, products, images, comments, and settings are unaffected.
Q: How often should I clean my WordPress database?
A: Monthly is sufficient for most sites. For WooCommerce stores with many daily orders, or high-traffic sites with many comments, monthly is the minimum — weekly for the most active sites.

Final Thoughts

A clean WordPress database is a faster WordPress database. The cleanup process — especially clearing revisions and expired transients on an older site — often produces more speed improvement per hour of work than any other optimisation task.

Add monthly database cleanup to your WordPress maintenance routine. It takes 5 minutes with WP-Optimize and contributes meaningfully to your site's performance over the long term.

Contact us to discuss professional WordPress performance optimisation
Views: 129