Performance

WordPress Database Optimization: Cleaning Up the Hidden Cause of Slow Sites

WordPress Database Optimization: Cleaning Up the Hidden Cause of Slow Sites

When a WordPress site gets slower over the years without any obvious change, the database is often the culprit. Nobody installed anything new. The theme is the same. But the admin feels sluggish, pages take longer to load uncached and the hosting company keeps suggesting an upgrade.

What usually happened is slow accumulation. Thousands of post revisions. Plugins that were deleted years ago but left their data behind. Logs that grew to millions of rows. A settings table loaded with megabytes of data on every single request.

Database optimization doesn’t get much attention because it’s invisible. But on sites that have been running for a few years, it often delivers some of the biggest speed improvements I can make. Here’s how I approach it.

Always back up first

Before running any cleanup, take a complete backup of the database and confirm you can restore it. Database changes are permanent. A mistaken query can delete content or break plugins in ways that are hard to undo.

Also do your first cleanup on a staging copy, so you can see the effect and check nothing breaks.

How WordPress uses the database

A standard WordPress database has a small number of core tables. The ones that matter most for performance are:

  • wp_posts, holding posts, pages, revisions, menu items, attachments and custom post types.
  • wp_postmeta, holding extra data for each post, often the largest table on sites with WooCommerce or page builders.
  • wp_options, holding site settings, plugin settings and transients.
  • wp_comments and wp_commentmeta.
  • wp_users and wp_usermeta.

Plugins add their own tables too, for logs, analytics, forms, scheduled actions and more.

Your table prefix might be different from wp_, which is fine. The examples below use the default prefix, so adjust them for your site.

Start by measuring

Before cleaning anything, find out where the problem actually is. A quick way to see table sizes:

The results usually tell a clear story. A 2GB log table from a security plugin. A huge postmeta table. An options table far bigger than it should be. Focus your effort where the size is.

The Query Monitor plugin is also invaluable. It shows slow queries on each page and which plugin or theme triggered them.

The options table and autoloading

The wp_options table deserves special attention, because of a setting called autoload. Options marked for autoloading are loaded on every single page request, whether they’re needed or not.

That’s fine for small, frequently used settings. It becomes a problem when plugins autoload large amounts of data, sometimes several megabytes. Every page load pays that cost.

Check the total size of autoloaded options:

WordPress 6.6 changed how autoload values are stored, which is why the query checks several values. As a rough guide, under a few hundred kilobytes is healthy. Above a megabyte deserves investigation.

Find the largest autoloaded options:

The option names usually reveal which plugin owns them. If the plugin is no longer installed, the option is often safe to remove, after a backup. If the plugin is active, you may be able to turn off autoloading for large options it doesn’t need on every page, but test carefully. WordPress’s Site Health screen also flags excessive autoloaded data in recent versions.

Post revisions

Every time you save a post, WordPress stores a revision. That’s a useful safety net, but on sites with frequent editing, revisions can number in the tens or hundreds of thousands.

You can limit future revisions in wp-config.php:

To remove existing revisions with WP-CLI:

On sites with a huge number of revisions, this command can hit limits because of the long list of IDs. In that case, use a database cleanup plugin that processes in batches, or delete in smaller chunks.

Removing revisions also removes their related postmeta, which keeps things tidy.

Expired transients

Transients are temporary cached values with an expiry time. When there’s no persistent object cache, they live in the options table. Expired transients should be cleaned up automatically, but in practice many linger.

If you see thousands of transients even after cleaning, a plugin may be creating them excessively. Identifying and fixing that plugin is better than cleaning up after it forever. Adding a persistent object cache like Redis moves transients out of the database entirely.

Spam and trashed content

Spam comments, trashed comments and trashed posts all sit in the database until permanently deleted.

If your site gets heavy comment spam, an anti-spam service prevents the buildup in the first place. If you don’t use comments at all, consider disabling them.

Action Scheduler tables

WooCommerce and many other plugins use a library called Action Scheduler to run background tasks. It stores tasks in tables like wp_actionscheduler_actions and wp_actionscheduler_logs.

Completed actions are supposed to be cleaned up after a retention period, but on busy sites, or when cleanup fails, these tables can grow to millions of rows. Failed actions can also pile up, sometimes revealing a broken integration.

Check the status in WooCommerce under Status, then Scheduled Actions. If you see huge numbers of completed or failed actions, investigate why before deleting. A failing task might mean an integration has been broken for months. Once understood, old completed and failed actions can be cleared, ideally with the tools the plugin provides.

Orphaned data from removed plugins

When you delete a plugin, it may leave behind options, tables, postmeta and cron jobs. Well-written plugins clean up on uninstall, but many don’t.

Look for:

  • Tables with names of plugins you no longer use.
  • Options with plugin prefixes that no longer apply.
  • Scheduled cron events for plugins that are gone.

Be careful here. It’s not always obvious which plugin created a table or option. Some are shared by several plugins from the same developer. If you’re unsure, rename a table rather than dropping it, check the site for a week, and only then remove it.

Postmeta bloat and indexes

The postmeta table often becomes the largest table on a site, especially with WooCommerce, page builders or heavy use of custom fields. Large postmeta tables slow down queries that filter or sort by meta values.

Some improvements:

  • Remove orphaned postmeta whose post no longer exists.
  • On WooCommerce stores, enable High-Performance Order Storage, which moves order data out of posts and postmeta into dedicated tables.
  • Consider better indexes. The default WordPress indexes on postmeta aren’t ideal for large sites. Plugins exist that add improved indexes safely, and they can noticeably speed up large sites.

Removing orphaned postmeta:

Run a SELECT version first to see how many rows would be deleted.

Storage engine and server settings

Older WordPress sites sometimes still have tables using the MyISAM storage engine. InnoDB is the modern default and handles concurrent access much better, with row-level locking and crash recovery. Converting is usually straightforward:

Do this during a quiet period, with a backup, as large tables can take time to convert.

If you manage your own server, check the database configuration too. The InnoDB buffer pool size, which controls how much data is kept in memory, is especially important. If it’s too small for your data, the database reads from disk far more often than it needs to.

Should you use OPTIMIZE TABLE?

After deleting a lot of data, tables can have unused space. Running OPTIMIZE TABLE rebuilds the table and reclaims that space. With InnoDB, the performance benefit is often modest, and on large tables it can lock the table temporarily. It’s worth running after major cleanups, at a quiet time, but it’s not something you need to do weekly.

Use cleanup plugins carefully

Database cleanup plugins make many of these tasks easier with a few clicks. They’re convenient, but treat them with care. Some offer aggressive options that delete data you might want, such as all revisions or all transients, including ones still in use. Always back up first, understand each option and avoid scheduling aggressive cleanups to run automatically without review.

Frequently asked questions

How often should I optimize my database?

For most sites, a light cleanup every few months is enough: expired transients, spam, trash and excess revisions. Large or busy sites benefit from monthly checks, especially of autoloaded options and plugin log tables.

Will cleaning my database make my site faster?

It depends on how bloated it is. Removing excessive autoloaded data and huge log tables can give dramatic improvements. Cleaning a small, healthy database won’t change much. Measure first, so you focus on what matters.

Is it safe to delete all revisions?

Generally yes, if you don’t need to restore older versions of your content. Many owners prefer to keep a few recent revisions per post as a safety net and remove the rest.

Can a bloated database cause errors?

Yes. Very large autoloaded data can cause memory issues, huge tables can slow backups until they time out and long-running queries can make the admin unresponsive. Regular maintenance prevents most of these problems.

Final thoughts

Your database quietly carries the weight of everything your site has ever done. Over time, it accumulates clutter that slows every page. Measure where the bloat is, tackle autoloaded options and oversized tables first, clean up revisions, transients and leftovers, and keep an eye on background task tables. Always back up, test on staging and remove things deliberately. A lean, healthy database is one of the most underrated foundations of a fast WordPress site.