WordPress Database Optimization:Revisions, Transients and Autoload

A WordPress database slows down mainly because of too much autoloaded data in wp_options, thousands of old revisions and expired transients, and leftover tables and settings from removed plugins. Cleaning these, carefully and with a backup, makes every page load and every admin screen lighter.

This guide explains what each kind of clutter is, how to check for it and how to remove it safely.

Before you start: back up

Database cleanup deletes data. Take a full database backup first (see WordPress backups: what to back up and how to restore) and, ideally, practise on a staging copy.

1. Autoloaded options: the most important check

The wp_options table stores settings. Rows with autoload set to load automatically are read into memory on every request, whether the page needs them or not. Plugins sometimes store large data there, and removed plugins often leave it behind.

Check the total autoloaded size in phpMyAdmin (adjust the table prefix):

SELECT SUM(LENGTH(option_value)) AS autoload_bytes
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');

WordPress's Site Health tool also warns when autoloaded options are unusually large. A total of a few hundred kilobytes is common; several megabytes deserves investigation.

Find the largest entries:

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

Option names usually start with the plugin's prefix. If an entry belongs to a plugin you have removed, it can generally be deleted. If it belongs to an active plugin, check the plugin's settings or support before changing anything.

2. Post revisions

WordPress saves a revision each time you update a post. Long-lived sites can have tens of thousands. To limit future revisions, add to wp-config.php:

define( 'WP_POST_REVISIONS', 10 );

Old revisions can be removed with a database-cleanup plugin or WP-CLI. Keep a reasonable number for recently edited content.

3. Expired transients

Transients are temporary cached values with an expiry time. Expired ones are not always removed promptly, especially on sites without a persistent object cache. Cleanup plugins and WP-CLI (wp transient delete --expired) remove them safely; WordPress recreates any it needs.

4. Spam, trash and orphaned data

• Spam and trashed comments.
• Trashed posts and pages.
• Orphaned post meta and comment meta, rows that refer to posts or comments that no longer exist.

Database-cleanup plugins handle these. Review what they propose before confirming.

5. Leftover plugin tables

Many plugins create their own tables and do not drop them when removed. In phpMyAdmin, look for tables with prefixes you do not recognise. Confirm which plugin created them (search the table name online or in the plugin's documentation) before dropping anything.

6. WooCommerce and Action Scheduler

On stores, the Action Scheduler tables can grow very large with completed and failed actions. Review Tools → Scheduled Actions, fix whatever causes repeated failures, and let old completed actions be purged. Store-specific advice is in how to speed up WooCommerce.

7. Optimise tables

After deleting large amounts of data, running Optimize table in phpMyAdmin (or OPTIMIZE TABLE) can reclaim space. On InnoDB tables this rebuilds the table, which can take time on large tables and should be done during quiet hours.

8. Add a persistent object cache

If the database is still a bottleneck for logged-in users and shops, a persistent object cache reduces how often WordPress queries it. See Redis object caching explained.

Worked example: a slow admin caused by autoload

A WordPress site's admin pages take several seconds to load even with few visitors. The autoload check shows about 6 MB of autoloaded options. The largest entries:

option_name	Size	Belongs to
old_slider_cache	3.1 MB	A slider plugin removed two years ago
rs_stats_log	1.4 MB	A statistics plugin still active, storing logs in options
widget_custom_html	40 KB	Active widgets (normal)

The fixes:

1. After a backup, delete old_slider_cache, since its plugin no longer exists.
2. For the statistics plugin, change its settings to store fewer logs, or replace it with a lighter solution; ask its support whether the data can be set to not autoload.
3. Re-run the check: autoloaded data drops to under 1 MB, and admin pages load noticeably faster.

Using WP-CLI for safe cleanups

If you have SSH access, WP-CLI makes routine cleanup scriptable and reviewable:

wp db export before-cleanup.sql          # backup first
wp transient delete --expired            # remove expired transients
wp post list --post_type=revision --format=count
wp db optimize                           # optimise tables

Deleting revisions in bulk is usually done with a cleanup plugin or a reviewed command; keep recent revisions for posts you are actively editing.

Safe cleanup routine

1. Back up the database.
2. Check autoloaded size and remove orphaned entries from removed plugins.
3. Limit and prune revisions.
4. Delete expired transients, spam and trash.
5. Review and drop leftover tables only after confirming their origin.
6. Optimise large tables during a quiet period.
7. Measure page and admin speed again.

Repeat a lighter version of this every few months as part of the WordPress maintenance checklist.

Frequently Asked Questions

Is it safe to use a database cleanup plugin?

Generally yes, if it is well maintained and you review what it will delete. Always back up first.

How big should a WordPress database be?

It depends on content, orders and logs. Size alone matters less than autoloaded data and missing indexes.

Will cleanup make my site faster?

It helps most when autoloaded data or huge tables are the cause. If the slowness comes from images or scripts, see how to speed up a WordPress website.

Related reading

For the WordPress overview, see WordPress hosting: what it is and how to choose, and compare ServerNeed WordPress hosting.

Sources

• WordPress Developer Resources: Database
• WP-CLI: wp transient