WordPress database optimization: revisions, transients and autoload

A site that has been running for a year does not have a database made only of posts and orders. Thousands of saved revisions, auto-drafts, expired transients and abandoned options from deleted plugins sit in it too. Most of these are harmless, but one of them really does slow the site down: heavy autoload options.

Before anything else, confirm that the database is actually the slow part; measuring beats guessing, and the path is described in speeding up WordPress. If the problem really is the database, continue.

Step zero: a backup#

Every delete command in this article is irreversible. Before you start, take a full export:

wp db export before-cleanup.sql

Without WP-CLI, use phpMyAdmin or your host's backup tool. A fuller routine is in automatic WordPress backups. In the SQL commands below we assume the table prefix is wp_; if your site's prefix is different, change it.

See where the space goes#

First, find out which tables are big:

wp db size --tables

Usually wp_posts, wp_postmeta and wp_options are at the top. If an unfamiliar table from an old plugin is huge, first find out whether it is still needed.

Autoload: the most important spot#

WordPress loads every option with autoload enabled on every request, before it renders the page. If the total is several megabytes, it slows every page of the site, even with partial caching. The query below shows the largest autoload options:

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

(Newer WordPress versions allow more values in the autoload column than just yes and no, which is why we list several.) Also check the total size:

SELECT ROUND(SUM(LENGTH(option_value)) / 1024) AS autoload_kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto', 'auto-on');

Now for each large option ask: which plugin does it belong to? Is it still active? If the plugin has been deleted, the record is an orphan and you can safely remove it. If the plugin is active but stores big data in autoload, do not delete the option; turn autoload off instead:

UPDATE wp_options SET autoload = 'no' WHERE option_name = 'option_name_here';

This is safer than deleting, and if something breaks it can be reversed. Leave alone options you do not recognize, especially ones that do not start with _transient and belong to the core or the theme (such as siteurl and active_plugins).

Expired transients#

A transient is a piece of temporary data (a cache) with an expiry date. WordPress removes some of them itself, but orphaned rows pile up. Deleting expired transients is harmless:

wp transient delete --expired

If you have a persistent object cache (such as Redis), transients are not stored in the database at all and this problem is much smaller.

Revisions and auto-drafts#

Every time you click "Save", WordPress creates a revision. On a site with several years of activity, these can outnumber the posts themselves many times over. We do two things: set a cap and delete the old ones. The cap goes in wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

And to delete existing revisions (if there are none, the command errors out, which is normal):

wp post delete $(wp post list --post_type=revision --format=ids) --force
wp post delete $(wp post list --post_status=auto-draft --post_type=any --format=ids) --force

The first command deletes all revisions, not just old ones. If recent history matters to you, use a cleanup plugin that works by age.

Abandoned tables and final optimization#

A plugin that has been deleted may still leave its tables. Match the table name to the plugin name, and delete it only when you are completely sure (and after a backup). After a large amount of data has been deleted, optimize the tables so the free space is returned:

wp db optimize

On shared hosting this command may lock a table for a few minutes; run it at a low-traffic hour.

Summary table#

If it is still slow after all this, look at WordPress image optimization or caching; the database is not the only factor.

Preventing buildup#

A one-time cleanup is not enough if the source of the problem is not closed. A few habits help. Delete unused plugins, not just deactivate them, and then check whether their tables and options remain. Before installing a new plugin, see what it writes to the database; plugins that write large logs (such as visit tracking or error logging) should have a cap or automatic cleanup.

If the site has heavy traffic, a persistent object cache also takes pressure off the database. But a cache is not a substitute for cleanup; it only hides the symptom. Indexing the columns used in heavy queries is a separate and more specialized topic that starts with monitoring slow queries.

Measurement tools#

If you want to know which queries are slow, debugging plugins that show queries in the dashboard are useful, as long as you enable them only temporarily and on a test site. Look at the query time and source columns: if one specific plugin runs dozens of heavy queries on every page, the problem is that plugin, not the database. In that case, first look at plugin conflicts and finding the faulty plugin.

Summary#

• Before any deletion, take a backup with wp db export or your host's tool.
• Heavy autoload options have the most impact; find the largest ones with a query.
• For an active plugin, turn autoload off and do not delete the record.
• Expired transients can be deleted safely.
• Limit revisions with WP_POST_REVISIONS and delete the old ones.
• After a large cleanup, run wp db optimize at a low-traffic hour.