Clean Up and Optimise the WordPress Database

The database is your WordPress site’s memory – and it grows with every post, every plugin and every saved setting. Over time, ballast accumulates that slows down every page build a little and bloats your backups. Cleaning it up pays off when you know where to start, and measure before and after instead of blindly clicking a plugin.

Note on all snippets: the table prefix is wp_ throughout. Your prefix is set in wp-config.php ($table_prefix) and may differ, for example wp7x_. Replace it accordingly, otherwise the query fails or finds nothing. The generator further down inserts your prefix for you.

Where the Ballast Accumulates

These places are almost always affected:

• Post revisions: WordPress stores every intermediate version. For long texts, that quickly adds up to many times the actual content.
• Transients: cached, supposedly temporary plugin data that doesn’t always expire cleanly and then lingers as leftovers.
• autoload data in wp_options: options that count as autoload are loaded on every page request. Abandoned plugins often leave large amounts of data here. This is the most important point.
• Trash and spam: deleted posts in the trash, spam and rejected comments along with their metadata.
• Orphaned metadata: postmeta and commentmeta entries whose post or comment was deleted long ago.
• Plugin job tables: on WooCommerce, mainly the Action Scheduler tables.

Measure First: Database and Table Sizes

Before you delete anything, look at the situation. With WP-CLI, one command gives you the total size and the size per table:

wp db size --tables --size_format=mb

Without WP-CLI, use phpMyAdmin or SQL. This query lists the fifteen largest tables of your installation; data_free is space InnoDB holds internally but is not using:

-- adjust the prefix wp_ if needed (it also appears in the LIKE pattern)
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb,
       ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.TABLES
WHERE table_schema = DATABASE() AND table_name LIKE 'wp\_%'
ORDER BY (data_length + index_length) DESC
LIMIT 15;

Write the numbers down. They are your “before”. Usually wp_posts, wp_postmeta and wp_options are at the top; on shops the Action Scheduler tables join them.

Optimizing wp_options: the Single Biggest Lever

The wp_options table is the control centre of your installation: everything from the site URL to each plugin’s settings ends up here. That is exactly why cleaning it up pays off the most. This section walks through what you need to know and do, step by step.

What autoload in wp_options means

Every row in wp_options has an autoload column. At the start of a request, WordPress loads all options marked as autoload into memory in one go. That way things like the site URL or the list of active plugins are available immediately, without querying each option individually. The trouble starts when plugins store large amounts of data in autoload options: every page request, every admin click and every cron run then carries that data along.

Since WordPress 6.6 the column knows more than yes and no. According to the WordPress developer docs and the Core blog, the values are:

The function wp_autoload_values_to_autoload() (since 6.6) returns the values that actually get loaded: by default yes, on, auto-on and auto. If you filter only on autoload = 'yes' as older guides do, you miss part of what is loaded on newer installs. Also since 6.6: an option larger than 150,000 bytes is no longer autoloaded by default unless it was explicitly set to (filter wp_max_autoloaded_option_size). Existing rows are not rewritten; they keep their value.

Measuring the autoload size

The total size of all loaded options in MB, using all four values WordPress loads since 6.6:

-- adjust the prefix wp_ if needed
SELECT ROUND(SUM(LENGTH(option_value)) / 1024 / 1024, 2) AS autoload_mb,
       COUNT(*) AS option_count
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

And the twenty largest entries:

SELECT option_name, autoload,
       ROUND(LENGTH(option_value) / 1024, 1) AS kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 20;

WP-CLI is shorter. According to the WP-CLI docs, --format=total_bytes returns the total size of the autoload options, and size_bytes is an additional field:

wp option list --autoload=on --format=total_bytes
wp option list --autoload=on --fields=option_name,size_bytes --format=csv | sort -t, -k2 -nr | head -20

Note that the WP-CLI docs list on and off as values for --autoload. Whether this catches every autoload variant depends on your WP-CLI version, so cross-check once with the SQL query above.

The Site Health check

Since WordPress 6.6 there is an autoload check under Tools → Site Health. According to the developer docs (WP_Site_Health::get_test_autoloaded_options()), it turns “critical” at 800,000 bytes of total autoload size; the threshold can be changed with the filter site_status_autoloaded_options_size_limit. The message reads “Autoloaded options could affect performance”. That is a rough warning line. Your site can suffer noticeably below it if one plugin owns most of the weight, and run fine above it. The list of the largest entries from the query above tells you more than the traffic light.

Spotting orphaned options from old plugins

In the list of largest entries, look for options that no longer belong to any active plugin or theme. Some hints:

• The option name often starts with the plugin’s prefix (elementor_, wpseo_, jetpack_). If a prefix shows up whose plugin is long gone, that is a candidate.
• Plugins like to store debug logs, API responses or large configuration arrays in options. You will spot them by sizes in the KB range under names like *_cache, *_log or *_data.
• Never delete by name alone. Search the option name in the code (wp-content/plugins, wp-content/themes, grep -r if needed). If it is still referenced, the option belongs to something that is active.
• Theme mods (theme_mods_<themename>) are not plugin leftovers. They belong to an installed theme, even when it is not currently active.

Transients: spotting and cleaning them safely

Transients are options with an expiry date. In the database they show up as a pair: _transient_<name> for the value and _transient_timeout_<name> for the expiry timestamp. For site transients the prefix is _site_transient_. According to the Transients docs, WordPress only clears expired transients occasionally by itself, so they can pile up. One special case: with a persistent object cache (Redis, Memcached), transients live in the cache and not in the database at all. Then there is little to gain in wp_options, though the cache itself can grow. More on that in our guide to WordPress caching.

Count the expired transients before you delete anything:

-- adjust the prefix wp_ if needed
SELECT COUNT(*) AS expired
FROM wp_options
WHERE option_name LIKE '\_transient\_timeout\_%'
  AND CAST(option_value AS UNSIGNED) < UNIX_TIMESTAMP();

WP-CLI is the cleanest way to delete them, because it uses WordPress’ own functions instead of raw SQL:

wp transient delete --expired

wp transient delete --all removes all transients, valid ones included. That is harmless, since plugins regenerate their transients when needed, but it costs some performance until the caches are filled again. For the normal case, --expired is enough. On multisite, --network covers the site transients.

Switching autoload off safely

Large options you don’t want to delete can be set to “don’t load automatically”. The entry stays; WordPress only loads it when code actually asks for it. With WP-CLI (since WP-CLI 2.8.0 according to the docs; on, off, yes and no are accepted):

wp option set-autoload name_of_the_option off

SQL works as well. I use no here because every WordPress version understands it, and since 6.6 it is treated like off:

-- adjust the prefix wp_ if needed
UPDATE wp_options SET autoload = 'no' WHERE option_name = 'name_of_the_option';

This is the safe first step because you can undo it any time with on or yes. There is a catch, though: a plugin may set the value back to autoload on its next update or save, and an option that really is needed on every request will be fetched individually afterwards. So after switching, check that the site and the admin area behave normally and that the size has actually dropped.

Deleting: only with a backup

Options whose plugin is demonstrably gone can be removed:

wp option delete name_of_the_option

Take a backup first, because deleting is final. How to do that is covered in our article on WordPress backups. Ideally a few days of observation sit between “switched to off” and “deleted”.

The effect

If the autoload size from the query above drops from several MB to a few hundred KB, every single page request feels it, with no caching plugin required. If it barely moves, the bottleneck is elsewhere.

Clean Up the Remaining Spots

Revisions and trash

Limit revisions permanently in wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

That keeps at most five revisions per post. false turns them off entirely (per the WordPress wp-config.php docs). Count existing revisions like this:

SELECT COUNT(*) FROM wp_posts WHERE post_type = 'revision';

For deleting, WP-CLI is better than a raw DELETE on wp_posts, because WordPress removes the related metadata too. According to the WP-CLI docs, --force skips the trash:

wp post delete $(wp post list --post_type=revision --format=ids) --force

The trash empties itself after 30 days according to the docs (EMPTY_TRASH_DAYS, default 30). Setting it to 0 disables the trash, and then there is no confirmation prompt on “Delete permanently”, so use that with care. Automatic removal of comment spam after 15 days applies on top.

Orphaned metadata in wp_postmeta

wp_postmeta often grows faster than any other table because plugins add many rows per post. A row is orphaned when its post no longer exists. Count first:

SELECT COUNT(*) AS orphans
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

Then, with a backup, delete:

DELETE pm
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;

The number of rows says nothing about their size. Check the table list from above to see whether wp_postmeta stands out at all before investing much time. The same pattern applies to wp_commentmeta (column comment_id against wp_comments).

Action Scheduler tables on WooCommerce

The Action Scheduler bundled with WooCommerce stores its jobs in tables prefixed actionscheduler_ according to its documentation (for example wp_actionscheduler_actions and wp_actionscheduler_logs). If the table list shows double-digit MB there, look for the cause: usually failing or very frequently repeating jobs, not missing maintenance. If you only delete rows without fixing the job, the problem is back soon. What else affects shop performance is in speeding up WooCommerce.

Indexes and storage engine

Two points that are less often the problem than people assume:

• Storage engine: modern WordPress installs run on InnoDB, which is the default engine in MySQL 8.4. Older installs sometimes still have MyISAM tables. See each table’s engine with SELECT table_name, engine FROM information_schema.TABLES WHERE table_schema = DATABASE();. Converting (ALTER TABLE wp_xyz ENGINE=InnoDB;) belongs in the hands of someone with a backup and a maintenance window.
• Indexes: core tables ship with their indexes. Missing indexes are more of an issue for plugins’ own tables. Check a slow query with EXPLAIN before you add any index. An index added on a hunch costs write performance and rarely helps.

The generator also creates the SQL for revisions, transients, spam and metadata with your table prefix, count first, then delete:

Also available as a standalone page with all guides: SQL generator.

Remove Table Overhead: What OPTIMIZE TABLE Does

After deleting, reserved but unused space remains. OPTIMIZE TABLE reclaims it, and what happens depends on the engine:

• InnoDB: according to the MySQL docs, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE. The table is rebuilt, index statistics are updated and unused space in the clustered index is freed. The docs also describe it as an online operation: an exclusive table lock is held only briefly during the prepare and commit phases. You may see the message “Table does not support optimize, doing recreate + analyze instead”. That is normal on InnoDB and not an error.
• MyISAM: here OPTIMIZE TABLE defragments the data file and reclaims space from deleted rows, most useful after deleting a large part of a table.

The effect is limited: disk space is freed and backups get smaller. Page speed rarely changes measurably. In phpMyAdmin it is one click on “Optimize table”; with WP-CLI it is wp db optimize (which runs mysqlcheck --optimize behind the scenes, per the docs). Plan it outside peak hours.

Important: make a backup before any database operation. A wrongly deleted option can take a site down.

Measure Afterwards

Run the same queries as at the start again: table sizes, autoload size and the list of the largest options. Compare with the numbers you wrote down, and look at load time (PageSpeed Insights, or better still the server response time in the browser’s network tab, which reflects the database more directly). That is the only way to know whether the cleanup actually helped rather than just feeling good. If the autoload size drops from several MB to a fraction, you’ve removed real ballast; if it barely moves, the problem was elsewhere, for example in plugins, hosting or missing caching.

How Often, and How It Stays Clean

A one-off cleanup fizzles out if the ballast just accumulates again. More sustainable is the combination: permanently limit revisions, let spam be discarded automatically, remove expired transients regularly, and check for autoload leftovers specifically after removing larger plugins. For most sites, a deliberate look once or twice a year is then enough. Exactly this kind of regular check is part of our WordPress maintenance.

A database that’s completely unreachable is a different problem → Error establishing a database connection. Otherwise, the database is one of the levers feeding into Core Web Vitals, alongside caching.

Want to know what’s really slowing your database down, without writing SQL yourself? Drop us a line – we’ll measure, clean up safely and show you the difference.