How to Clean Up and Optimise Your Website’s Database for Better Performance

Website databases grow quietly in the background. Every draft you save, every comment that gets flagged as spam, every plugin or extension that stores its own settings — it all accumulates in the same set of database tables. On a site that’s been running for a year or two, that accumulation can add up to tens of thousands of redundant rows sitting in your database doing nothing useful.

Most of this data is completely harmless, but it has a cost. A bloated database means slower queries, larger backup files, and occasionally sluggish admin performance. On shared hosting where server resources are limited, those costs are more noticeable. Cleaning up your database periodically is one of the simplest maintenance tasks you can do, and it pays dividends in both performance and reliability.

This guide covers what accumulates in a website database, how to clean it safely, and how to keep it lean going forward.

What Builds Up in a Website Database

Before cleaning anything, it helps to understand what you’re dealing with. The main sources of database bloat are:

• Post revisions — Every time you save a post or page in the editor, many platforms save a new revision. A post edited 30 times can end up with 30 stored copies. These are rarely needed after a post is published, but some platforms — WordPress included — keep them indefinitely by default.
• Auto-drafts — Many editors auto-save your work as you type. These auto-draft records accumulate in the database and are usually left over from sessions that were closed without being published or discarded properly.
• Trashed posts and pages — Posts you’ve moved to trash remain in the database until you empty the bin. If you never empty it manually, they sit there indefinitely.
• Spam and trashed comments — Spam comments caught by an anti-spam tool (Akismet, on WordPress) or the platform itself are held in the comments table. On a site receiving regular spam, this table grows quickly.
• Temporary cached data — Plugins and extensions often cache the results of expensive operations to avoid repeating them (WordPress calls these “transients,” stored in the wp_options table). They’re supposed to expire automatically, but expired entries aren’t always cleaned up, leaving orphaned rows behind.
• Orphaned metadata — When a post, page, or user is deleted, its associated metadata is not always removed from the database along with it.
• Unused plugin data — Plugins and extensions frequently add their own tables or settings rows. When you deactivate or delete one, that data often stays behind unless it explicitly cleans up after itself.

Before You Clean: Back Up First

Database cleanup is generally safe, but it’s irreversible. Once you delete revisions or orphaned data, it’s gone. Before running any cleanup tool, make sure you have a recent backup of your database. This is non-negotiable — especially on a live site.

If you don’t already have a backup routine in place, set one up before proceeding. A structured backup schedule ensures you always have a restore point before making changes like this.

How to Clean Your Website’s Database

There are two main approaches: using a plugin or tool built for this, or accessing the database directly. For most site owners, a plugin or tool is the right choice. Direct database access is better suited to developers who are comfortable with SQL.

Option 1: Use a Database Cleanup Plugin or Tool

On WordPress, several plugins handle this well — two of the most widely used are WP-Optimize and Advanced DB Cleaner, both available free from the plugin directory.

WP-Optimize is the more straightforward option for beginners. Here’s how to use it:

1. Install and activate WP-Optimize from Plugins → Add New.
2. Go to WP-Optimize → Database in your admin menu.
3. You’ll see a list of optimisations available — post revisions, auto-drafts, trashed posts, spam comments, expired transients, and more. Each row shows how many items are found.
4. Select the items you want to clean. For a first-time cleanup, I usually select all of them. The plugin shows you counts before you commit, so you can make informed decisions.
5. Click Run all selected optimizations.
6. After cleanup, use the Optimize button to run a MySQL OPTIMIZE TABLE command, which defragments the tables and reclaims freed space.

After running the optimisation, WP-Optimize will show you the space saved. On sites I’ve cleaned after a year or more of activity, it’s common to reclaim anywhere from 20MB to several hundred MB depending on how active the site has been.

Option 2: Clean via Direct Database Access

If you prefer direct access, most hosting control panels provide a database management tool — phpMyAdmin is the most common, including on WordPress hosting. Log in, select your database, and you can run SQL queries or use the built-in table interface to delete rows manually.

These specific queries target WordPress’s own table structure (adjust table and column names for your platform’s schema if different). To remove all post revisions, for example, you would run:

DELETE FROM wp_posts WHERE post_status = 'inherit' AND post_type = 'revision';

To clear expired transients:

DELETE FROM wp_options WHERE option_name LIKE '_transient_%' OR option_name LIKE '_site_transient_%';

Note that the default WordPress table prefix is wp_, but yours may differ. Check your wp-config.php file if you’re unsure. After running DELETE queries, go to the Operations tab for each affected table and run Optimize table to reclaim the freed space.

Direct SQL is powerful but unforgiving. A mistake here can corrupt your data, which is exactly why the backup step above is essential.

Controlling Post Revisions Going Forward

Cleaning up existing revisions is a one-time fix. To prevent the same bloat from building up again, most platforms let you configure how many revisions to keep. On WordPress, add a revision limit to your wp-config.php file — open it via your hosting file manager or FTP and add this line before the /* That's all, stop editing! */ comment:

define( 'WP_POST_REVISIONS', 3 );

This tells WordPress to keep a maximum of three revisions per post. That’s enough to roll back a recent mistake without accumulating dozens of copies over time. You can also set it to false to disable revisions entirely, though I’d advise keeping at least a small number for safety.

Managing Autoloaded Options

Some platforms store settings that get loaded on every single page request, whether they’re actually needed for that page or not. If plugins or extensions add large or unnecessary entries here, it becomes a real, often-overlooked performance bottleneck. On WordPress, this is the wp_options table’s autoloaded options.

You can check the total size of your autoloaded options by running this query in phpMyAdmin:

SELECT SUM(LENGTH(option_value)) AS total_autoload_size FROM wp_options WHERE autoload = 'yes';

The result is in bytes. If your total autoloaded data exceeds roughly 800KB, it’s worth investigating which options are responsible. WP-Optimize and similar tools can help identify the largest rows, and you can disable autoloading on non-essential plugin options if the plugin doesn’t handle this itself. WordPress 6.4 and later added a built-in mechanism to manage autoloaded options more granularly, so keeping WordPress updated helps here too.

The broader subject of website performance — including caching, image optimisation, and server-level improvements — is covered well in the WordPress developer documentation on optimisation.

Automating Database Cleanup

Running a manual cleanup every few months works fine for small sites. For larger or busier sites, scheduling the cleanup to run automatically is more reliable. On WordPress, WP-Optimize includes a scheduler that lets you set cleanup tasks to run daily, weekly, or monthly. Enable it under WP-Optimize → Settings → Schedule.

On WordPress, the scheduler works through the platform’s own built-in task system. If you want a reliable scheduling setup, it’s worth understanding how cron jobs work and whether your hosting is configured to run them properly.

Common Mistakes

• Cleaning without a backup — Always back up your database before running any cleanup. It takes a couple of minutes and gives you a safety net if something goes wrong.
• Deleting all revisions on a live, actively edited site — If your team is actively editing posts, deleting all revisions removes the ability to roll back recent changes. Clean older revisions and set a reasonable limit going forward rather than removing everything.
• Running OPTIMIZE TABLE on InnoDB tables during peak traffic — On MyISAM tables, OPTIMIZE TABLE locks the table while it runs. On InnoDB (the modern default), this is less of an issue, but it’s still best to run optimisations during low-traffic periods.
• Ignoring the options/settings table — Most people focus on posts and comments and overlook where settings are stored. Orphaned plugin options and bloated autoloaded data here can be a significant and often overlooked drag on performance.

When Manual Cleanup Makes Sense vs Automation

For small personal sites with low edit frequency, a manual cleanup once every few months is perfectly adequate. A plugin like WP-Optimize makes it a five-minute task. For sites with a lot of editorial activity — multiple authors, frequent publishing, heavy plugin use — automating the cleanup on a weekly or fortnightly schedule saves time and keeps the database consistently lean.

Either way, database cleanup should be part of a broader maintenance routine. Keeping your database lean, running regular backups, and staying on top of website performance as a whole will keep your site stable and fast as it grows over time.

If cleanup and optimisation aren’t enough and your site starts throwing connection errors instead, see how to fix a database connection error for the specific steps to diagnose and resolve that failure.

Conclusion

Start with a backup, run a cleanup tool to clear out revisions, temporary cached data, spam, and orphaned data — on WordPress, that’s WP-Optimize — then set a revision limit (wp-config.php on WordPress) to prevent the same bloat from returning. That’s the entire process for most sites — simple, safe, and worth doing at least once a year.