WordPress database cleanup

What accumulates in a WordPress database

WordPress databases grow from a handful of tables into hundreds of megabytes around mostly invisible layers: post revisions, trashed items, transients, spam comments, and one very real problem, the wp_options table holding autoloaded bloat. None of these produce errors, but they slow queries, bloat backups, and eat memory on autoload. Cleanup is a series of small deletions, not a redesign of the schema.

This tutorial assumes you have shell or phpMyAdmin access and a backup. If you do not have a backup, stop and make one now; the only dangerous part of cleanup is data loss, and the only way to lose data is a query that deletes too much without a rollback path.

Step 1: Back up everything

A database backup plus the files makes the cleanup reversible:

wp db export /backups/pre-clean.sql

Test the export completes and is a valid SQL text file. The earlier restore from backup tutorial explains how to restore it if anything goes wrong.

Step 2: Remove post revisions

Each edit of a post stores a complete copy. A heavily-edited post can leave dozens. The safe target: every revision except the latest, across the whole posts table:

DELETE a,b,c
FROM wp_posts a
LEFT JOIN wp_term_relationships c ON (a.ID = c.object_id)
LEFT JOIN wp_postmeta b ON (a.ID = b.post_id)
LEFT JOIN wp_term_taxonomy d ON (d.term_taxonomy_id = c.term_taxonomy_id)
WHERE a.post_type = 'revision';

Or via WP-CLI:

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

Step 3: Remove transients and expired data

Transients (cached values with a timeout in wp_options) pile up when plugins never expire them, and scheduled events with the same kind accumulate in wp_actionscheduler rows. Clear transients:

wp transient delete --all
wp cron event run --due-now

Then remove orphaned action-scheduler rows if the table is present:

DELETE FROM wp_actionscheduler_actions
WHERE status = 'complete';

Step 4: Attack the autoloaded data in wp-options

The single biggest WordPress performance leak is autoloaded data in wp_options. Every autoloaded row is fetched into memory on every page load, so a 10 MB autoloaded blob is paid on every visit. Find the biggest. On modern WordPress (WP-CLI 2.x and recent core) the autoload column stores auto or on for values loaded on every request, off for the rest, so match all autoloaded states, not only the legacy yes:

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

If your table's rows still only hold yes, the same query matches those too, because the list above includes the legacy value. The classic offenders are cron storage, page builders, and plugin settings holding serialized site snapshots. Remove or archive only rows you recognize; never delete a row because it is large if a plugin owns it.

Step 5: Clear spam and trash before hunting orphans

Comments and posts in the spam and trash buckets still occupy rows and slow queries. Remove spam comments (and trash comments) first:

wp comment delete $(wp comment list --status=spam --format=ids) --force
wp comment delete $(wp comment list --status=trash --format=ids) --force

If wp comment delete rejects the ids as a bare list on your WP-CLI version, delete each returned id one at a time instead. Empty the trash posts and pages the same way:

wp post delete $(wp post list --post_status=trash --format=ids) --force

Step 6: Remove orphaned postmeta and garbage

Orphaned postmeta for deleted posts (rows whose post_id no longer exists) accumulate quietly:

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

Run the equivalent for wp_term_relationships only if you are comfortable that terms attached to truly deleted posts are expendable; terms still used by live content must survive.

Step 7: Repair and optimize the tables

The deletions leave fragmentation, so let the engine reclaim it after the cleanup is proven:

wp db repair
wp db optimize
# or from SQL
OPTIMIZE TABLE wp_options, wp_posts, wp_postmeta;

wp db repair also checks table health, worth running before and after big deletes.

Verification table

When to stop

• Do not delete timestamps or plugin-owned options you cannot identify; a database plugin stores configuration, not just cache.
• Delete in one operation type per session. Revisions, then transients, then orphans, each verified, beats a sweep of every table.
• Cleanup is maintenance, not security. If the bloat is large because of abuse (rentable comments, an exposed XML-RPC), fix the access path first and clean the table after.