The Problem
WordPress site owners notice their dashboard feels sluggish after a year or two of publishing. Pages load slower, the editor stutters, and backups balloon past 200 MB even on small blogs. The culprit is database bloat. Post revisions stack dozens deep. Expired transients never get cleaned. Spam comments wait in the trash. Orphan metadata lingers from uninstalled plugins. This tutorial trims your wp_posts, wp_options, and wp_postmeta tables without breaking content or settings. You’ll back up first, then remove the bloat in seven steps.
What You’ll Need
- WordPress version: 6.4 or newer, running on PHP 8.0+ and MySQL 5.7+
- Permissions needed: Administrator role in WordPress, plus database access through your hosting panel
- Time to complete: 20 minutes for sites under 5 GB, 45 minutes for larger sites
- Difficulty level: Intermediate (you will run raw SQL queries against your live database)
- Prerequisites: A current backup taken in the last hour, and access to phpMyAdmin from your hosting control panel
Step 1: Back Up Your Database
Before you change a single row, create a full backup. Open your WordPress dashboard and go to Plugins → Add New. Search for UpdraftPlus, install it, then click Activate. Open Settings → UpdraftPlus Backups. Click Backup Now. In the dialog, tick both Include the database in the backup and Include any files in the backup. Click Backup Now again. Wait for the green “Backup Successful” message before continuing.
[SCREENSHOT: updraftplus-backup-now-dialog]
Want to test the SQL queries first? Set up a WordPress staging site and run them there before touching production.
Step 2: Limit Post Revisions in wp-config.php
WordPress keeps every revision of every post. A blog with 200 posts and ten revisions each stores 2,000 extra rows. Open wp-config.php via FTP or your hosting File Manager. Find the line that reads:
/* That's all, stop editing! Happy publishing. */Paste this above that line. Place it the same way you would when adding code to functions.php.
What this does: caps revisions at 5 per post and autosaves every 60 seconds instead of 30.
define( 'WP_POST_REVISIONS', 5 );
define( 'AUTOSAVE_INTERVAL', 60 );Save the file. The cap applies only to future edits. Existing revisions stay until you delete them.
[SCREENSHOT: wp-config-revisions-constant]
Step 3: Delete Old Post Revisions
Open phpMyAdmin from your hosting panel. Select your WordPress database in the left sidebar. Click the SQL tab. Paste this query:
What this does: removes every row from wp_posts whose type is revision.
DELETE FROM wp_posts WHERE post_type = 'revision';Click Go. phpMyAdmin reports how many rows were deleted. If your table prefix is not wp_, replace wp_posts with yourprefix_posts.
[SCREENSHOT: phpmyadmin-delete-revisions-query]
Step 4: Clear Expired Transients
Transients are temporary cached values. Expired ones often pile up in wp_options and never get purged. Run this query in the same SQL tab:
What this does: deletes every expired transient and its matching timeout row.
DELETE FROM wp_options
WHERE option_name LIKE '\_transient\_%'
OR option_name LIKE '\_site\_transient\_%';Click Go. Plugins recreate the transients they actually need on the next page load.
[SCREENSHOT: phpmyadmin-clear-transients]
Step 5: Empty Spam and Trashed Comments
Back in your WordPress dashboard, open Comments. Click the Spam filter. Click Empty Spam. Click the Trash filter. Click Empty Trash. For blogs with thousands of spam comments, use SQL instead:
What this does: removes every comment marked as spam or moved to trash.
DELETE FROM wp_comments
WHERE comment_approved = 'spam'
OR comment_approved = 'trash';[SCREENSHOT: wordpress-empty-spam-comments]
Step 6: Remove Orphan Postmeta
Uninstalled plugins often leave metadata attached to posts that no longer exist. Run:
What this does: deletes metadata rows whose parent post is gone.
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;Click Go. On a neglected site this query can remove tens of thousands of rows.
[SCREENSHOT: phpmyadmin-orphan-postmeta-query]
Step 7: Compact Your Database Tables
Click Structure in phpMyAdmin while your database is selected. Tick the box at the top of the table list to select every table. From the With selected dropdown, pick the table-compaction routine (the option labelled Optimize table). phpMyAdmin reclaims the disk space freed by your earlier deletions. The page reloads with an “OK” status next to each table.
[SCREENSHOT: phpmyadmin-optimize-all-tables]
Reload your site’s homepage. Pages should now render noticeably faster. Your next backup should be smaller.
Troubleshooting
Error: “Error establishing a database connection” after a SQL query.
Fix: restore your backup immediately and check the query — a missing WHERE clause can delete entire tables.
Error: UpdraftPlus times out on large sites during step 1.
Fix: increase max_execution_time in your hosting PHP settings to 300 seconds, or back up tables individually from phpMyAdmin’s Export tab.
Error: phpMyAdmin returns “MySQL server has gone away” mid-query.
Fix: split the query into smaller batches using LIMIT 1000 and re-run until zero rows return.
Error: Site renders blank pages after cleanup.
Fix: disable all plugins via Settings → Plugins, then reload the homepage. Reactivate one plugin at a time to find the conflict.
Quick Recap
- You backed up the site twice before changing any rows.
- You capped future revisions in
wp-config.phpto keep bloat from returning. - You removed old revisions, expired transients, spam comments, and orphan metadata.
- You compacted every table to reclaim disk space.
- For a deeper performance pass after cleanup, follow How to Set Up a Cloudflare CDN for WordPress Step by Step.
