Routine WordPress database cleanup matters once a site has been through content edits, comment spam, transient-heavy caching, and plugin churn. Removing short-lived records and rebuilding tables does not solve every performance problem by itself, but it does reduce avoidable table growth and makes backups, incident response, and later troubleshooting easier.
WordPress stores posts, comments, metadata, options, and plugin state in MySQL or MariaDB tables. The safest cleanup path is usually WP-CLI because it can remove WordPress objects such as expired transients, unwanted revisions, and spam comments before handing the table-maintenance step to mysqlcheck through wp db optimize.
Examples assume shell access in the active document root, a working wp command, and a fresh database dump kept outside the web root. Revision cleanup is a content-policy decision rather than a blind performance tweak, and the optimize pass can rebuild InnoDB tables or surface MySQL 8 SQL-mode issues on legacy zero-date columns, so table-level status lines matter more than the final Success line alone.
Steps to optimize a WordPress database with WP-CLI:
- Change into the exact WordPress document root and confirm the command is targeting the expected site.
$ cd /var/www/example.com/public_html $ wp option get home https://www.example.com
On multisite, add --url=<site-url> to the remaining commands so the cleanup runs against the intended site instead of the network default.
- Export a rollback copy of the current database before deleting anything.
$ mkdir -p ~/backups/wordpress $ wp db export ~/backups/wordpress/pre-optimize-$(date +%Y%m%d%H%M%S).sql Success: Exported to '/home/user/backups/wordpress/pre-optimize-20260329034926.sql'.
Keep the SQL dump outside the public document root. Cleanup mistakes are faster to reverse from a fresh export than from piecing rows back together manually.
Related: How to back up a WordPress site
- Remove expired transients that no longer need to stay in the database.
$ wp transient delete --expired Success: 1 expired transient deleted from the database.
A 0 or No expired transients found result is normal when the site is already clean or when a persistent object cache stores most transient data outside the database.
- 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 15 13 2026-03-29 03:48:22 14 13 2026-03-29 03:48:21
Keep the revisions that still matter to the editorial workflow. A busy site can intentionally retain recent revisions even after a cleanup pass.
- Delete only the revision IDs that should no longer be kept.
$ wp post delete 15 14 --force Success: Deleted post 15 14.
Revision deletion is permanent. Review the list first instead of turning this into a blanket cron job without an agreed retention policy.
- 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 2 13 spammer $ wp comment list --status=trash --fields=comment_ID,comment_post_ID,comment_author --format=table comment_ID comment_post_ID comment_author 3 13 trasher
Listing the records first keeps the cleanup auditable and avoids deleting comments that are only pending moderation.
- Permanently remove the spam and trash comments returned by the review step.
$ wp comment delete 2 3 --force Success: Deleted comment 2. Success: Deleted comment 3.
- Run the table optimization pass after the application-level cleanup is finished, and read the table-level statuses instead of trusting only the final line.
$ wp db optimize wordpress.wp_commentmeta note : Table does not support optimize, doing recreate + analyze instead status : OK wordpress.wp_comments note : Table does not support optimize, doing recreate + analyze instead error : Invalid default value for 'comment_date' status : Operation failed ##### snipped ##### Success: Database optimized.
The recreate + analyze instead note is normal for InnoDB tables. It means the storage engine is using its supported maintenance path rather than the older MyISAM behavior.
If any table reports Operation failed or Invalid default value, treat that as an unresolved result even when wp ends with Success: Database optimized. On some MySQL 8 servers, stricter SQL modes reject legacy zero-date defaults on core WordPress tables, so leave those tables unchanged until the SQL-mode issue is reviewed.
- Re-run the narrow cleanup checks and keep the pre-optimize dump until the site has passed normal traffic.
$ wp transient delete --expired Success: No expired transients found. $ wp post list --post_type=revision --format=count 0 $ wp comment list --status=spam --format=count 0 $ wp comment list --status=trash --format=count 0
If revisions are intentionally retained, the final revision count can stay above 0 by design. The important result is that only the records approved for deletion are gone and the site still behaves normally afterward.
Mohd Shakir Zakaria is a cloud architect with deep roots in software development and open-source advocacy. Certified in AWS, Red Hat, VMware, ITIL, and Linux, he specializes in designing and managing robust cloud and on-premises infrastructures.
