Quick answer: WordPress database optimization means removing the clutter that builds up over time (old post revisions, spam and trashed comments, expired transients, leftover plugin data and oversized autoloaded options) and then optimising the tables so MySQL can read them efficiently. Take a full backup first, clean up with a plugin such as WP-Optimize or WP-Sweep or by hand in phpMyAdmin, and then limit revisions and review autoloaded data so the database stays lean.
Every page on a WordPress site is built from the database. When someone visits a post, WordPress runs queries to fetch the content, the settings, the menus and whatever your plugins need. On a new site those queries are quick. After a few years of publishing, editing, installing and removing plugins, the database can be several times larger than it needs to be, and some of that bulk is loaded on every single request. Caching hides part of the problem for visitors, but the admin area, logged-in users, searches and checkouts all hit the database directly.
What fills up a WordPress database
Before cleaning anything, it helps to know where the clutter comes from.
Post revisions
Each time you save a post or page, WordPress stores a full copy as a revision. Autosaves add more. A post that has been edited fifty times can have fifty copies of itself in the wp_posts table. Revisions are useful, and our guide to WordPress revisions explains how to use them, but most sites keep far more than they need.
Spam, trashed and unapproved comments
Spam comments caught by Akismet sit in the database until they are deleted. Trashed comments stay for 30 days by default. On a busy blog these can number in the tens of thousands, along with their rows in wp_commentmeta.
Transients
Transients are temporary cached values that plugins and themes store in the wp_options table, each with an expiry time. WordPress removes expired transients when they are next requested, but transients that are never requested again can be left behind. Some plugins create a great many of them.
Autoloaded options
The wp_options table holds site settings. Rows marked autoload = yes are loaded into memory on every page request, whether or not that page needs them. When plugins store large amounts of data as autoloaded options, or leave them behind after being deleted, every request gets slower.
Orphaned data from old plugins
Deleting a plugin does not always remove its data. Tables, options and post meta can stay in the database for years after the plugin is gone.
Orphaned meta
Post meta, comment meta and term relationships can be left pointing at posts or comments that no longer exist.
Step 1: Back up before you touch anything
Database cleanup deletes data, and some of it cannot be recovered. Take a full backup of the database first, and make sure you know how to restore it. A backup plugin such as UpdraftPlus or BackupBuddy works, as does an export from phpMyAdmin or a backup from your host’s control panel.
If you can, test the cleanup on a staging copy of the site before running it on the live site.
Step 2: Check the size of your tables
Log in to phpMyAdmin through your hosting control panel and select your WordPress database. The table list shows the size of each table and its overhead. This tells you where to focus.
On most sites, the biggest tables are wp_posts, wp_postmeta and wp_options. If a table you don’t recognise is large, its prefix or name usually points to the plugin that created it. Check whether that plugin is still installed before deciding what to do.
Step 3: Clean up with a plugin
For most site owners, a cleanup plugin is the safest route. Three good options:
- WP-Optimize removes revisions, auto-drafts, spam and trashed comments, expired transients and more, then optimises tables. It can run on a schedule.
- WP-Sweep uses WordPress’s own delete functions rather than direct SQL, so related data is removed properly. It covers revisions, auto-drafts, orphaned meta, duplicated meta, transients and unused terms.
- Advanced Database Cleaner adds the ability to find orphaned tables and options left by old plugins, as well as scheduled cleanups.
Whichever you use, read each option before ticking it. “Remove all revisions” is fine on most sites. Removing unapproved comments may delete real comments waiting for moderation.
Step 4: Clean up by hand (for developers)
If you are comfortable with SQL, you can do the same work directly in phpMyAdmin. Replace wp_ with your table prefix.
To see how much data is autoloaded, run:
SELECT SUM(LENGTH(option_value)) FROM wp_options WHERE autoload = 'yes';
To find the largest autoloaded options:
SELECT option_name, LENGTH(option_value) AS size
FROM wp_options WHERE autoload = 'yes'
ORDER BY size DESC LIMIT 20;
There is no fixed rule for how much autoloaded data is too much, but if the total runs into megabytes, it is worth looking at. Check what each large option belongs to. If it comes from a plugin you have removed, you can delete it. If it belongs to an active plugin, ask the plugin developer before changing it.
Be careful with direct deletes. Removing revisions with a plain DELETE query can leave orphaned rows in wp_postmeta and wp_term_relationships. A tool like WP-Sweep, which uses WordPress’s own delete functions, is safer for most tasks.
Step 5: Optimise the tables
After deleting data, tables can hold unused space. Optimising them reclaims that space and reorganises the data.
In phpMyAdmin, tick the tables and choose “Optimize table” from the dropdown. WP-Optimize and similar plugins do the same thing with one click.
InnoDB vs MyISAM
Older WordPress sites often use the MyISAM storage engine, while newer MySQL installs default to InnoDB. InnoDB handles many reads and writes at once better, because it locks individual rows instead of whole tables, and it recovers better after a crash. For busy sites, especially WooCommerce stores, converting tables to InnoDB is often worthwhile. Do it during a quiet period, with a backup, and check with your host first.
While you are checking engines, note that WordPress has used the utf8mb4 character set since version 4.2, which supports the full range of characters including emoji. If an older site was not converted during that update, your host may be able to help.
Step 6: Stop the clutter coming back
Cleaning once helps, but the database will grow again. A few settings keep it in check.
Limit revisions
Add this line to wp-config.php to keep only the last five revisions of each post:
define( 'WP_POST_REVISIONS', 5 );
You can also lengthen the autosave interval:
define( 'AUTOSAVE_INTERVAL', 120 );
Empty the trash sooner
WordPress empties the trash every 30 days. To shorten that to a week:
define( 'EMPTY_TRASH_DAYS', 7 );
Schedule regular cleanups
WP-Optimize and Advanced Database Cleaner can run cleanups weekly or monthly. Set one up and check the results now and then.
Be choosy about plugins
Before installing a plugin, consider whether you really need it. When you remove one, check its settings for an option to delete its data, and look for leftover tables afterwards.
Database optimization and overall speed
A clean database is one part of a fast site. It matters most for uncached requests: the admin area, logged-in users, WooCommerce carts and checkouts, and site search. For everything else, page caching does most of the work. Our guides to WordPress caching plugins and WordPress speed optimization cover the rest of the picture, from hosting to images.
If your host supports PHP 7.0, upgrading it also makes WordPress run noticeably faster, including the code that talks to the database. Test your theme and plugins on a staging copy before switching.
FAQ
How often should I optimise my WordPress database?
For most sites, once a month is plenty. Busy blogs or WooCommerce stores may benefit from weekly cleanups of transients and spam.
Is it safe to delete all post revisions?
Yes, as long as you don’t need the older versions. Take a backup first, and keep a revision limit in place afterwards.
Will database optimization make my site faster?
It helps most on sites with large, cluttered databases and on pages that can’t be cached. On a small, new site the difference may be hard to notice.
Can I delete tables left by old plugins?
Yes, once you are sure the plugin is no longer used. Check the table name, confirm the plugin is gone and back up before dropping it.
Keep your site quick
A tidy database keeps your admin area responsive and your uncached pages fast, which matters most on the pages where people buy or enquire. If you want help with a slow WordPress site, see how we help businesses turn visitors into customers.
