How to Optimize Your WordPress Database: Revisions, Bloated Tables and Cleanup

If your site has slowed down for no clear reason, look at the database too. That is where WordPress keeps your posts, pages, settings, users and comments. Over time, it also collects a lot of data you no longer need. WordPress database optimization means removing that data and leaving the tables clean.

Below you will find what piles up, how to clean it safely, with a backup first, and how to stop the bloat from coming back.

Why the WordPress database gets bloated

WordPress stores far more than the content you see. Every post keeps a history of its edits. Many plugins create their own tables and settings. Themes leave settings behind even after you uninstall them.

That is how a database of a few megabytes can grow to hundreds of megabytes. The leftover data makes the difference. A bloated database makes every query (the request the site uses to read data) slower. Backups take longer, and under heavy traffic the risk of stalls goes up.

Revisions, the most common source of bloat

Every time you save a page or a post, WordPress creates a revision, which is a copy of the content. A post edited 40 times has 40 copies in the wp_posts table. Across hundreds of posts, revisions become by far the leading source of bloat.

Do not remove them entirely. They let you go back to an earlier version, for example when you roll back WordPress after a failed update. Limit how many are kept instead. Open wp-config.php (the site’s settings file) and add the line below above the comment that starts with “That’s all, stop editing”:

define( 'WP_POST_REVISIONS', 5 );

From now on, WordPress keeps the last 5 revisions per post. It deletes the older ones by itself, the next time you save a change to that post.

What else clogs the tables

• Expired transients: temporary cache data that plugins leave in the wp_options table long after it expired.
• Spam and pending comments: there can be thousands, each with its own data.
• The Trash: posts, pages and comments that were deleted, but not permanently.
• Orphaned metadata: rows left behind by plugins or posts that no longer exist.
• Orphaned tables: created by uninstalled plugins and themes that never cleaned up after themselves.

Some of this data slows the site down directly. Autoloaded options in wp_options are read on every page load. A few megabytes of useless autoloaded options can slow down the entire site, on every visit.

I wrote about them separately, in autoload options and transients: cleaning up the WordPress database. The official Transients API documentation notes that a transient can disappear at any time, and the plugin that created it must be able to rebuild it.

What you gain after a cleanup

A clean database answers queries faster. Pages load faster, and the admin panel responds more quickly. Backups get smaller and run faster. Moving to another server is simpler.

The server also does less work. That matters a lot on shared hosting, where resources are split with other sites.

Before anything else: a verified backup

Deleting from the database has no undo button. One wrong command can delete the very tables your whole site depends on. That is why the first step is a full backup: the files and the database.

Then check that the backup can actually be restored. It is not enough for the file to exist. You will find the steps in the guide on how to back up a WordPress site.

Ideally, you clean up a test copy of the site (staging) first. You apply the cleanup to the live site only after you have seen that everything works. Find out what a WordPress staging site is and how to use it.

WordPress database optimization, step by step

Order matters: delete first, then optimize the tables, and check the site at the end.

1. Delete useless data, one category at a time

For most sites, a plugin is enough, with no code. WP-Optimize or Advanced Database Cleaner do the cleanup from a simple interface. Tick revisions, spam comments, expired transients, orphaned metadata and the Trash one by one.

Do not tick everything on the first pass. Go category by category and check the site between steps.

If you use WP-CLI (the tool that runs WordPress commands from a terminal), two commands handle part of the cleanup: expired transients and revisions.

wp transient delete --expired
wp post delete $(wp post list --post_type=revision --post_status=inherit --format=ids) --force

The first deletes expired transients. The second permanently deletes all revisions, including the versions you could roll back to. Run it only after a backup. The $(...) syntax works in bash-type terminals, on Linux and macOS. It does not work in PowerShell or cmd on Windows.

Do not improvise DELETE queries directly in MySQL on the WordPress tables unless you know exactly what you are removing. This is where most irreversible accidents happen.

2. Optimize the tables

After deleting, run the OPTIMIZE TABLE command. It rebuilds the tables and can reclaim the freed space. You will find it in the plugin and in phpMyAdmin (the web interface for the database). Written in SQL, it looks like this:

OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options;

If your table prefix is not wp_, change it in the command. You can see it in wp-config.php, under $table_prefix. On InnoDB tables, MySQL replies with “Table does not support optimize, doing recreate + analyze instead”. That message is normal: the table is rebuilt anyway.

On MyISAM tables, MySQL locks the table while the command runs. Run it when the site has few visitors.

3. Check the site

Open a few pages, the admin panel and your forms. If something does not work, restore the backup you made at the start.

The table engine: InnoDB or MyISAM

Also check which storage engine your tables use. InnoDB handles concurrent writes better and is more resistant to data corruption than the old MyISAM. Older sites may still have MyISAM tables. In phpMyAdmin, the engine shows in the table list, in the “Type” column.

You convert them one table at a time, again after a backup:

ALTER TABLE wp_posts ENGINE=InnoDB;

After the conversion, check the site again.

How to stop the bloat from coming back

Besides the revision limit, you can empty the Trash automatically. Add this to wp-config.php, also above the “That’s all, stop editing” comment:

define( 'EMPTY_TRASH_DAYS', 7 );

WordPress then empties the Trash by itself after 7 days. Without this line, it does so after 30 days.

A one-time cleanup fixes today’s problem, but the database bloats again within a few months. Schedule a monthly cleanup and cap revisions from the start.

Database cleanup in WordPress maintenance

If you would rather not handle it yourself, database optimization is included in some of our WordPress maintenance plans. You can see what each plan includes on the pricing page. If you want to know the state of your site first, ask for a free audit.

Read also: Why you need a changelog for your website.