Over time a WordPress database collects material nobody needs: old revisions, expired temporary data, spam comments, settings left behind by removed plugins. Cleaning it can make backups smaller and some queries faster. It can also break a site if done carelessly. This tutorial covers what is safe to remove, in what order, and what to leave alone.
Set expectations first
Database cleanup is maintenance, not a speed miracle. On a small site the visible difference is often modest. If the site is slow, caching, image sizes and hosting quality usually matter more. See Website Performance Basics. Clean the database because it keeps the site tidy and backups manageable, and because one specific kind of clutter, autoloaded options, can affect every page.
Step 1: Back up, and test the backup
Database changes cannot be undone. Before anything else, export the database and confirm the file is complete. Better still, do the cleanup on a staging copy first. How to Create a Basic Website Backup and Test a Restore shows how.
Step 2: Look before you delete
In your host’s database tool, open the database and sort the tables by size. Note:
- Which tables are largest.
- Whether there are tables that belong to plugins you no longer use.
- The overall size, so you can compare afterwards.
The standard WordPress tables are posts, postmeta, options, comments, commentmeta, users, usermeta, terms, termmeta, term_taxonomy, term_relationships and links, each with your table prefix. Anything else was added by a plugin or theme.
Step 3: Remove the low-risk clutter
These items are safe to delete on almost any site.
- Spam and trashed comments. Empty them under Comments.
- Trashed posts and pages. Empty the trash once you are sure nothing is needed.
- Auto-drafts. Leftovers from the editor.
- Expired transients. Temporary cached values that are past their expiry.
- Old post revisions. WordPress keeps every saved version. Keeping the last few per post is enough for most sites.
A reputable database cleanup plugin can do all of this from one screen. Choose one with a clear list of what each option removes, run it, and deactivate it if you do not need it permanently. If you use WP-CLI, the command wp transient delete –expired handles transients.
Step 4: Limit future revisions
To stop revisions accumulating, add this line to wp-config.php above the line that says to stop editing:
define( 'WP_POST_REVISIONS', 5 );
WordPress then keeps the five most recent revisions of each post.
Step 5: Optimize the tables
After deleting rows, the tables can be optimized to reclaim space. In phpMyAdmin, select the tables and choose Optimize table. With WP-CLI, run wp db optimize. On modern database engines the gain is small, and it is enough to do this occasionally.
Step 6: Check autoloaded options
The options table holds site settings. Rows marked to autoload are loaded on every page request. Plugins sometimes store large amounts of data there, and it stays after the plugin is removed.
- Open Tools, Site Health. Recent versions of WordPress warn when autoloaded options are unusually large.
- A cleanup or diagnostic plugin can list the largest autoloaded options by name.
Only remove an option when you can identify it by its name as belonging to a plugin you have uninstalled. If you are not sure what an option is, leave it.
The risky part: what not to do
- Do not delete tables because the name looks unfamiliar. Find out which plugin created them. Remove them only if that plugin is gone for good.
- Do not run SQL commands copied from the internet without understanding each line and having a tested backup.
- Be cautious with orphaned metadata options. Tools that delete post meta or user meta without a matching parent are usually right, but some plugins use these tables in unusual ways.
- Do not delete all transients on a busy shop during trading hours. They are rebuilt, but the site may be slow while that happens.
- Do not clean a shop’s order or session data without checking the shop plugin’s own tools first. WooCommerce has built-in cleanup options under its status tools.
- Do not change the table prefix or storage engine as part of a routine cleanup.
Step 7: Verify
- Browse the site: home, posts, pages, forms and, if relevant, the shop and checkout.
- Log out and in again.
- Check Tools, Site Health for new errors.
- Compare the database size with the figure you noted.
- Keep the pre-cleanup backup for at least a couple of weeks.
A sensible routine
- Empty spam and trash monthly.
- Remove expired transients and old revisions every few months.
- Review tables and autoloaded options after uninstalling a plugin, and once a year.
- When removing a plugin, use its own uninstall or remove data option if it has one.
Add these to your Monthly Website Maintenance Checklist.
Common mistakes
- Cleaning without a backup.
- Running every option of a cleanup plugin without reading what it does.
- Dropping tables that belong to an active plugin.
- Expecting a slow site to become fast from cleanup alone.
