WordPress Database Optimization – Revisions, Transients, Autoload

A WordPress site that started at 50MB database can grow to multiple GB over years. Most of that growth isn’t your content — it’s accumulated revisions, expired transients, orphaned data, and bloated autoloaded options. Big databases slow down every page load. This guide covers the four main culprits and how to clean them safely.

Check your database size first

cPanel → MySQL Databases. List shows database name and size.

Or via phpMyAdmin: select database → see size at top.

Healthy ranges:

• Small blog — 10-50MB.
• Active business site — 50-200MB.
• WooCommerce — 100MB-1GB depending on order volume.
• Heavy site / years old — Multiple GB usually means bloat.

If your database is much bigger than seems justified by content, there’s cleanup to do.

1. Post revisions

WordPress saves a new revision every time you save a post. Over years, a single post can have hundreds of revisions stored. Each revision is a near-duplicate of the full post content.

Limit future revisions

Add to wp-config.php (above “/* That’s all…” comment):

define('WP_POST_REVISIONS', 5);

Keep last 5 revisions per post. Older ones automatically pruned. 5 is plenty for “let me revert that last change.”

Clean existing revisions

Plugin: WP-Optimize — Database tab → check “Clean post revisions” → Run optimization.

Or via WP-CLI:

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

For sites with thousands of posts and millions of revisions, can free hundreds of MB.

2. Transients

Transients are temporary cached data WordPress stores in the database. Many plugins use them — feed cache, API response cache, computed values. They expire automatically but expired ones often aren’t deleted immediately. Over time, the wp_options table accumulates them.

Clean expired transients

WP-Optimize plugin → check “Clean expired transient options” → Run.

Or via WP-CLI:

wp transient delete --expired

Some sites accumulate gigabytes of expired transients. Cleanup is a meaningful win.

Nuclear option for transients

wp transient delete --all

Removes ALL transients including non-expired. Mostly safe — plugins regenerate as needed. Run after major cleanup.

3. Autoloaded options (the silent killer)

This is the most impactful and least-known optimization. The wp_options table has an autoload column. Options with autoload=yes are loaded on EVERY page request — every time. If you have 10MB of autoloaded options, every page load reads 10MB from the database.

Check your autoload size

phpMyAdmin → run this query (replace wp_ if your prefix is different):

SELECT 
  ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS autoload_size_mb,
  COUNT(*) AS autoload_count
FROM wp_options 
WHERE autoload='yes';

Healthy: under 1MB. Concerning: 5-10MB. Disaster: 20MB+.

Find the biggest autoloaded options

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

Shows the 20 largest autoloaded values. Often you’ll see deactivated plugins’ old data, log accumulators, oversized session storage.

Disable autoload on culprits

For specific known offenders, change autoload from ‘yes’ to ‘no’:

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

Or use plugin “Autoload Optimizer” for safer GUI management.

Caution: some plugins genuinely need their options autoloaded. Test after changes; if something breaks, switch that option back to autoload=yes.

4. Orphaned post meta and comment meta

When you delete a post, its post_meta entries should be deleted too. Sometimes they aren’t — leaving orphaned rows that grow over years.

WP-Optimize → check “Remove orphaned post meta” and “Remove orphaned comment meta” → Run.

Or via WP-CLI:

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

5. Spam comments and trashed items

Spam comments and trashed posts/comments stay in database until purged.

WP-Optimize options:

• Clean spam comments.
• Clean trashed comments.
• Clean trashed posts/pages.
• Clean unapproved comments older than X days.

6. Optimize tables

After deletions, MySQL/MariaDB doesn’t immediately reclaim space. Optimize tables to compact:

phpMyAdmin → select database → check all tables → “Optimize table” from dropdown.

Or via WP-CLI:

wp db optimize

Reclaims disk space. Doesn’t change site behavior; just shrinks files.

Schedule ongoing cleanup

WP-Optimize and similar plugins offer scheduled cleanup. Weekly or monthly:

• Clean revisions older than 30 days.
• Delete expired transients.
• Optimize tables.

Set and forget. Database stays lean.

Backup first, always

Before any database cleanup operation:

1. Trigger a JetBackup, or
2. Export via cPanel → MySQL → phpMyAdmin → Export.

If anything breaks (rare but possible), restore from backup.

Common questions

“How often should I clean?” Monthly auto-clean catches accumulation. Manual quarterly review catches what auto-clean misses.

“Will cleanup speed up my site?” Yes, especially if autoloaded options are bloated. Lighter DB = faster every query.

“WooCommerce specific cleanup?” Yes — WooCommerce stores lots of action scheduler data, customer sessions, and abandoned cart data. WP-Optimize + plugins like “Action Scheduler” cleaner handle these.

“After cleanup, database file size on disk didn’t shrink.” Need to optimize tables (compact) after deleting. Re-run optimization step.

“Deactivated plugins leave data behind — can I clean that?” Yes, plugins like “Plugins Garbage Collector” find orphaned plugin data. Use carefully — test thoroughly after.

What’s next

• WordPress maintenance routine: WordPress maintenance.
• Backups: JetBackup.
• phpMyAdmin basics: phpMyAdmin guide.

Most WordPress sites have an order of magnitude more database content than needed. An hour of cleanup with WP-Optimize and the queries above commonly cuts database size by 50-80% and noticeably speeds every page load. Schedule weekly cleanup; revisit manually quarterly. The database stays healthy without ongoing attention.