SQL queries to clean orphaned data and purge the WordPress database

An old WordPress database can build up metadata with no associated object, revisions and temporary data. Old or large does not mean expendable. This guide helps you identify candidates and prepare a limited clean-up; it is not a batch of commands to paste into production.

Prepare a backup and define the site's scope

Check the actual database and prefix in wp-config.php. The examples use wp_. On multisite, user tables can be shared and each site has its own set of tables: do not apply these examples without reviewing that structure.

Rehearse on a clone. Before cleaning production, prepare a recent backup and control writes from visits, cron, queues and integrations. Showing a maintenance page does not necessarily stop all those writes.

From the WordPress installation, export to a private path outside the web roots. In this example, first create ~/copias-docs with 700 permissions and check that it is not published:

umask 077
wp db export "$HOME/copias-docs/antes-limpieza.sql" &&
sha256sum "$HOME/copias-docs/antes-limpieza.sql" > "$HOME/copias-docs/antes-limpieza.sql.sha256"

If the export fails, the file may be incomplete: do not continue. Use new names so that you do not overwrite an earlier backup, download the backup and check a restore into a temporary database. The checksum checks integrity; it does not prove that the restore works.

Inspect orphaned post metadata

First run the count and a sample in phpMyAdmin:

SELECT COUNT(*) AS filas_afectadas
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

SELECT pm.meta_id, pm.post_id, pm.meta_key
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL
LIMIT 50;

Work out why they exist and whether there are incomplete imports or processes. If you have confirmed they are expendable, this is the equivalent delete:

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

On large tables, the developer should prepare bounded batches and control locking; do not run a mass delete just because the sample is small.

Inspect orphaned user metadata

SELECT COUNT(*) AS filas_afectadas
FROM wp_usermeta um
LEFT JOIN wp_users u ON um.user_id = u.ID
WHERE u.ID IS NULL;

SELECT um.umeta_id, um.user_id, um.meta_key
FROM wp_usermeta um
LEFT JOIN wp_users u ON um.user_id = u.ID
WHERE u.ID IS NULL
LIMIT 50;

Only after reviewing the set, the multisite context and the backup, the equivalent delete is:

DELETE um FROM wp_usermeta um
LEFT JOIN wp_users u ON um.user_id = u.ID
WHERE u.ID IS NULL;

Transactions and caches

If all the modified tables are InnoDB, you can rehearse a single reviewed delete inside a transaction, in the same database session:

START TRANSACTION;
-- Ejecuta un solo DELETE revisado y comprueba el recuento.
ROLLBACK;

Do not use COMMIT until you have completed the planned checks. MyISAM and structural changes do not offer the same way back; do not convert tables as an improvised step in a clean-up. A transaction can hold locks while it is open.

Direct SQL does not run the WordPress APIs or update all its caches. After a validated clean-up, invalidate the affected caches with a known scope, without indiscriminately flushing the Redis of other websites.

Empty tags

List the tags, including empty ones, and check their count column:

wp term list post_tag --hide_empty=0 --fields=term_id,name,count

A zero value is a candidate for review, not an order to delete: there may be unpublished content or an editorial reason to keep the tag. To delete a confirmed tag, use the API through WP-CLI, replacing the placeholder with its real ID:

wp term delete post_tag ID_REVISADO

Do not delete taxonomy relationships directly with SQL. Afterwards, check links, navigation and related content.

Posts whose author no longer exists

Do not delete them or reassign every content type without reviewing them. This query finds candidates:

SELECT p.ID, p.post_type, p.post_status, p.post_title, p.post_author
FROM wp_posts p
LEFT JOIN wp_users u ON p.post_author = u.ID
WHERE u.ID IS NULL
LIMIT 50;

Some records may legitimately use author 0. For a post whose authorship needs correcting, choose a valid user and use the WordPress editor or wp post update with the reviewed IDs. That way the application operations that a direct UPDATE would skip are run.

Revisions and transients

To review old revisions, use a UTC date consistent with the column:

SELECT ID, post_parent, post_modified_gmt
FROM wp_posts
WHERE post_type = 'revision'
  AND post_modified_gmt < DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 DAY)
ORDER BY ID
LIMIT 50;

Ten days is only an example. Decide the retention according to your needs and delete selected revisions with a tool that uses the WordPress APIs, not by deleting rows from wp_posts in bulk and leaving their metadata behind.

To review transients without showing their values and then remove only the expired ones:

wp transient list --fields=name,expiration

wp transient delete --expired

The list includes transients that have not expired; check their expiry. With a persistent cache or multisite, check the tool's scope before interpreting what appears in the tables.

When you finish, validate the front end, admin area, users, orders and tasks. Keep the backup during the observation period. If you have to restore, coordinate new writes so that you do not lose orders or changes made after the backup.