How to optimize a WordPress database

Keeping the WordPress database lean matters once a site has started accumulating revisions, expired transients, spam comments, and other short-lived records that no longer help serve visitors. Cleanup does not make every slow site fast on its own, but it does reduce avoidable table growth and makes later troubleshooting easier.

WordPress stores posts, metadata, options, comments, and plugin settings in MySQL or MariaDB tables. The safest cleanup path is usually WP-CLI because it understands WordPress objects and uses supported commands for transients, posts, comments, and database maintenance instead of deleting rows blindly with raw SQL.

The steps below assume WP-CLI already works from the site document root and that the account running it can reach the active database. Export a backup before writing changes, treat revision cleanup as a content-policy decision rather than a pure performance tweak, and schedule table optimization during a low-traffic window because OPTIMIZE TABLE can rebuild large InnoDB tables.

1. Open a terminal session in the WordPress document root.

   $ cd /var/www/html

2. Export a fresh database backup before deleting anything.

   $ mkdir -p ~/backups/wordpress
   $ wp db export ~/backups/wordpress/pre-optimize-2026-03-25.sql
   Success: Exported to '/home/user/backups/wordpress/pre-optimize-2026-03-25.sql'.

   A cleanup mistake is usually faster to undo from a database export than from piecing records back together manually.

   Related: How to back up a WordPress site

3. Remove expired transients that no longer need to stay in the database.

   $ wp transient delete --expired
   Success: 1 expired transient deleted from the database.

   This only removes database-backed expired transients. Sites using a persistent object cache can store many transients outside wp_options, so a small or zero delete count does not always mean caching is misconfigured.

4. Review the current revision inventory before deleting rollback history.

   $ wp post list --post_type=revision --fields=ID,post_parent,post_date --format=table
   ID  post_parent  post_date
   42  17           2026-03-25 00:40:33
   41  17           2026-03-25 00:40:32

   Revisions are useful on editorial sites. Keep the newest ones when authors still rely on post-level rollback, and only delete the IDs that are genuinely disposable.

5. Delete the revision IDs that are no longer needed.

   $ wp post delete 42 41 --force
   Success: Deleted post 42.
   Success: Deleted post 41.

   Deleting revisions removes those recovery points permanently. Do not turn this into a blanket cron job unless the site's editorial workflow has been reviewed first.

6. Review spam and trash comments before purging them.

   $ wp comment list --status=spam --fields=comment_ID,comment_post_ID,comment_author --format=table
   comment_ID  comment_post_ID  comment_author
   120         17               spammer
   
   $ wp comment list --status=trash --fields=comment_ID,comment_post_ID,comment_author --format=table
   comment_ID  comment_post_ID  comment_author
   121         17               removed-user

   Listing the records first keeps the cleanup auditable and avoids deleting comments that are only pending moderation.

7. Permanently remove the spam and trash comments returned by the previous step.

   $ wp comment delete 120 121 --force
   Success: Deleted comment 120.
   Success: Deleted comment 121.

8. Run the database optimization pass after the application-level cleanup is finished.

   $ wp db optimize
   wp_example.wp_options
   note     : Table does not support optimize, doing recreate + analyze instead
   status   : OK
   wp_example.wp_posts
   note     : Table does not support optimize, doing recreate + analyze instead
   status   : OK
   ##### snipped #####
   Success: Database optimized.

   That recreate + analyze instead message is normal for InnoDB tables. It means the storage engine is using its supported maintenance path rather than the older MyISAM behavior.

   On large or busy sites, this step can be I/O-heavy and may briefly hold metadata locks. Run it during a maintenance window instead of peak traffic.

9. Re-run the checks that match the cleanup scope and confirm only intentional records remain.

   $ wp transient delete --expired
   Success: 0 expired transients deleted from the database.
   
   $ wp post list --post_type=revision --fields=ID,post_parent,post_date --format=table
   ID  post_parent  post_date
   
   $ wp comment list --status=spam --format=count
   0
   
   $ wp comment list --status=trash --format=count
   0

   If revisions are still present by design, the final revision list should contain only the posts that were intentionally kept out of the cleanup.