2026年11月14日

How to Optimize the WordPress Database (Clean Up with WP-CLI)

Database & Backups

This article is a localized edition of the Japanese original. Read the Japanese original

When you run WordPress for a long time, post revisions, spam and trashed comments, expired transients (temporary cached data) and the like build up in the database. This article explains how to use WP-CLI to check how much of each there is, delete what you do not need, and then tidy up the tables (as of November 2026, tested with WordPress 7.1 and WP-CLI 2.12).

Deleted data cannot be restored. Work through the steps in order, and take a backup before you start.

Step 0: Take a backup

# Run in the WordPress directory (any file name will do)
cd /var/www/html
wp db export ~/backup-before-optimize.sql

Keep the exported file outside the document root. For backup strategy, see also how to back up the WordPress database.

Step 1: Check what has built up

First, look at the numbers to see where the size is.

# Size of the whole database and of each table
wp db size --human-readable
wp db size --tables --format=table

# Table prefix (check it, since it may not be wp_)
wp db prefix

# Largest tables first: data, index and unused space (MB)
wp db query "SELECT table_name,
    ROUND(data_length/1024/1024, 2) AS data_MB,
    ROUND(index_length/1024/1024, 2) AS index_MB,
    ROUND(data_free/1024/1024, 2) AS free_MB
    FROM information_schema.tables
    WHERE table_schema = DATABASE()
    ORDER BY (data_length + index_length) DESC;"

# Number of revisions
wp post list --post_type=revision --format=count

# Number of spam and trashed comments
wp comment list --status=spam --format=count
wp comment list --status=trash --format=count

# Number of transient rows (value rows and expiration rows combined)
wp option list --transients --format=count

wp option list does not count transients unless you add --transients. Also, on sites that use a persistent object cache such as Redis, transients are stored in the cache rather than in the database.

If wp_options is large, the cause is often autoloaded options, so check it with how to optimize the wp_options table.

Step 2: Delete unneeded data

When there is nothing to delete, wp post delete and wp comment delete return an error if you pass them no IDs at all. The examples below run the deletion only when there are IDs.

Delete revisions

IDS=$(wp post list --post_type=revision --format=ids)
[ -n "$IDS" ] && wp post delete $IDS --force

wp post delete also removes the metadata attached to revisions. Deleting directly from wp_posts with SQL DELETE leaves the metadata behind and does not handle a different table prefix, so use the WP-CLI command. If you have posts you may want to roll back to an earlier version, keep those posts' revisions first or skip this step.

Delete spam and trashed comments

IDS=$(wp comment list --status=spam --format=ids)
[ -n "$IDS" ] && wp comment delete $IDS --force

IDS=$(wp comment list --status=trash --format=ids)
[ -n "$IDS" ] && wp comment delete $IDS --force

Delete expired transients

wp transient delete --expired

# To delete all transients (the site may be slower until caches are rebuilt)
# wp transient delete --all

Delete metadata whose post no longer exists (orphaned data)

Deleting plugins and the like can leave rows in postmeta whose parent post is gone. Count them, then delete them.

P=$(wp db prefix)

# Check the count
wp db query "SELECT COUNT(*) FROM ${P}postmeta pm
    LEFT JOIN ${P}posts p ON pm.post_id = p.ID
    WHERE p.ID IS NULL;"

# Delete
wp db query "DELETE pm FROM ${P}postmeta pm
    LEFT JOIN ${P}posts p ON pm.post_id = p.ID
    WHERE p.ID IS NULL;"

# Do the same for comment metadata
wp db query "DELETE cm FROM ${P}commentmeta cm
    LEFT JOIN ${P}comments c ON cm.comment_id = c.comment_ID
    WHERE c.comment_ID IS NULL;"

Step 3: Optimize the tables (OPTIMIZE TABLE)

wp db optimize

wp db optimize runs mysqlcheck --optimize (mariadb-check on MariaDB) on every table in the database. For InnoDB tables you will see output like the following, which is normal.

wp.wp_posts
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

According to the official MySQL manual, OPTIMIZE TABLE on InnoDB rebuilds the table, updates the index statistics and reorganizes unused space. For regular InnoDB tables it is performed as online DDL, and an exclusive lock is taken only briefly at the start and at the end (tables with a FULLTEXT index are the exception and are locked while it runs). On the other hand, because the table is rebuilt, it temporarily uses disk space.

It is most effective right after you delete a large number of rows. InnoDB leaves some free room in its pages by design, so periodically optimizing tables you have not deleted from makes almost no difference to speed. Running it once after Step 2, at a low-traffic time, is enough.

If you use phpMyAdmin, check the target tables in the database's table list and choose optimize from the bulk-action menu.

Step 4: Keep it from building up

Limit the number of revisions

Setting WP_POST_REVISIONS in wp-config.php lets you decide how many revisions to keep per post. Put it in wp-config.php, not functions.php, above the "That's all, stop editing!" line (or the equivalent line in a localized wp-config.php).

<?php
// Add above the "That's all, stop editing!" line in wp-config.php
// Number of revisions to keep per post (true = unlimited, false or 0 = none)
define( 'WP_POST_REVISIONS', 5 );

You can also write it with WP-CLI.

wp config set WP_POST_REVISIONS 5 --raw

This setting does not delete existing revisions all at once. The next time a post is saved, its oldest revisions beyond the limit are deleted.

Autosave (AUTOSAVE_INTERVAL, 60 seconds by default) overwrites a single autosave per post per user, so lengthening the interval barely reduces the number of rows. This constant also has no effect if you put it in functions.php, because it is read too late.

WordPress already deletes expired transients daily

WordPress core schedules a task called delete_expired_transients to run once a day when an admin screen is opened, and it deletes expired transients. You can check whether it is scheduled with the following command.

wp cron event list --fields=hook,next_run_relative,recurrence | grep delete_expired_transients

On sites where WP-Cron does not run, this deletion does not run either. To run WP-Cron from the server's cron, see how to run cron with WP-CLI.

Notes

Take a backup before deleting anything, and do the work at a low-traffic time.
DELETE in wp db query runs without confirmation. Check the count with SELECT COUNT(*) before you run it.
For plugins that have their own tables, such as WooCommerce, clean up their data with the plugin's own features. Deleting it directly with SQL can break consistency.

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)