Over time, your WordPress database accumulates unnecessary data that slows down your site. Regular optimisation keeps your database lean and your pages loading fast.
What Bloats Your Database?
| Data Type | Description | Typical Bloat | |---|---|---| | Post revisions | Every save creates a new revision | Can be 10x your actual posts | | Transients | Cached data with expiry dates | Thousands of expired entries | | Spam comments | Comments caught by Akismet/spam filters | Grows indefinitely | | Trashed items | Deleted posts, pages, and comments | Sits in trash until purged | | Orphaned meta | Metadata for deleted posts/comments | Accumulates silently | | Table overhead | Fragmented space from deletions | 5–20% of table size |
Always back up your database before optimising. Use cPanel > Backup Wizard or a plugin like UpdraftPlus.
Method 1: WP-Optimize Plugin (Recommended)
WP-Optimize is a trusted, free plugin that handles all database cleanup tasks.
Install and Run
- Go to Plugins > Add New and search for WP-Optimize
- Install and activate the plugin
- Go to WP-Optimize > Database
- You will see a list of cleanup tasks with the number of items to remove
Cleanup Tasks
Check the following and click Run all selected optimizations:
- Clean all post revisions
- Clean all auto-draft posts
- Clean all trashed posts
- Remove spam comments
- Remove trashed comments
- Remove expired transient options
- Remove orphaned postmeta
- Remove orphaned commentmeta
- Remove orphaned relationships
- Optimise database tables
Schedule Weekly Cleanup
- Go to WP-Optimize > Settings
- Enable Scheduled cleanup
- Set to run Weekly
- Select the tasks you want automated
- Click Save settings
Method 2: Manual phpMyAdmin Cleanup
For more control, run SQL queries directly in phpMyAdmin.
In cPanel, open phpMyAdmin, select your WordPress database, and go to the SQL tab.
Delete Post Revisions
DELETE FROM wp_posts WHERE post_type = 'revision';
Delete Expired Transients
DELETE FROM wp_options WHERE option_name LIKE '_transient_%';
DELETE FROM wp_options WHERE option_name LIKE '_site_transient_%';
Delete Spam and Trashed Comments
DELETE FROM wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_comments WHERE comment_approved = 'trash';
Delete Orphaned Post Meta
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;
Delete Orphaned Comment Meta
DELETE cm FROM wp_commentmeta cm
LEFT JOIN wp_comments c ON cm.comment_id = c.comment_ID
WHERE c.comment_ID IS NULL;
Optimise All Tables
In phpMyAdmin, go to the Structure tab, select all tables, and choose Optimize table from the dropdown at the bottom.
If your table prefix is not
wp_, adjust the queries accordingly.
Method 3: WP-CLI (Advanced)
If you have SSH access:
wp db optimize
wp post delete $(wp post list --post_type=revision --format=ids) --force
wp transient delete --all
wp comment delete $(wp comment list --status=spam --format=ids) --force
Limit Future Revisions
Add to wp-config.php to limit revisions per post:
define( 'WP_POST_REVISIONS', 5 );
Set to false to disable revisions entirely, or a number (like 5) to keep the most recent ones.
How Often to Optimise
| Site Type | Recommended Frequency | |---|---| | Personal blog | Monthly | | Business site | Bi-weekly | | WooCommerce store | Weekly | | High-traffic site | Weekly or more |
Impact on Performance
A bloated database forces MySQL to scan more rows for every query. After optimisation you can expect:
- Faster page loads: Database queries complete faster
- Reduced hosting resource usage: Less CPU and memory for MySQL
- Smaller backups: Less data to back up and restore
- Improved admin speed: The WordPress dashboard becomes more responsive