Clean up and optimise the WordPress database

Over time a WordPress database collects thousands of post revisions, spam comments, expired temporary data and leftovers from removed plugins. A bloated database makes backups slower and, in the case of autoloaded options, slows every single page load. A periodic clean-up keeps it lean.

Before you start

• Back up the database: cPanel → Files → Backup → Download a MySQL Database Backup, or wp db export.
• Check its size in cPanel → Databases → Manage My Databases, where each database's size is listed.

Clean up with LiteSpeed Cache

1. Go to LiteSpeed Cache → Database → Manage.
2. Review the counts for Post Revisions, Auto Drafts, Trashed Posts, Spam Comments, Trashed Comments and Expired Transients.
3. Click each item to clean it, or Clean All.
4. Click Optimize Tables to reclaim space.

Other plugins such as WP-Optimize do the same. Deactivate the plugin when you are finished if you only need it occasionally.

Limit revisions going forward

Add to wp-config.php above the "stop editing" line:

define( 'WP_POST_REVISIONS', 10 );
define( 'AUTOSAVE_INTERVAL', 120 );

Find the largest tables

In cPanel → Databases → phpMyAdmin, select the database; the Size column shows each table. Common heavy tables:

For log tables, set a retention period in the plugin's settings rather than emptying them by hand. Completed scheduled actions can be cleared under Tools → Scheduled Actions in WooCommerce.

Check autoloaded options

Options marked as autoload are loaded on every request. Run this in phpMyAdmin's SQL tab (adjust the prefix):

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

Large entries from plugins you deleted can be removed. If unsure, search the option name online to identify the plugin. Tools → Site Health also warns when autoloaded data is too large.

Remove leftovers from deleted plugins

Many plugins leave tables and options behind when deleted. Before removing a table, confirm the name belongs to a plugin you no longer use, and take a backup. Never delete core tables such as posts, postmeta, options, users, usermeta, terms, term_taxonomy, term_relationships, comments and commentmeta.

Optimise with WP-CLI

wp transient delete --expired
wp post delete $(wp post list --post_type=revision --format=ids) --force
wp db optimize

Troubleshooting

• Clean-up times out: clean one item type at a time or use WP-CLI.
• Something broke after deleting a table: restore the backup; the table belonged to an active plugin.
• Optimize Tables reports nothing to do: InnoDB tables reclaim space differently; the clean-up still removed the rows, and the file size shrinks on the next table rebuild.
• Database grows again quickly: a plugin logs heavily or cron jobs fail and pile up; check scheduled actions and log settings.

Next steps

• WordPress speed checklist
• Optimise and repair tables in phpMyAdmin
• Run WP-Cron reliably