Database Optimization Guide

Optimizing Your WordPress Database

Over time, WordPress databases accumulate overhead from revisions, transients, spam comments, and fragmented tables. Regular optimization keeps your site fast and your database lean.

Check Database Size

sudo wp db size --tables --path=/home/DOMAIN/public_html --allow-root

This shows each table’s size. Look for unusually large tables like wp_options, wp_postmeta, or wp_actionscheduler_*.

Clean Post Revisions

WordPress stores every revision of every post. On an active site, this can mean thousands of unnecessary rows:

# Count revisions
sudo wp post list --post_type=revision --format=count --path=/home/DOMAIN/public_html --allow-root

# Delete all revisions
sudo wp post delete $(sudo wp post list --post_type=revision --format=ids --path=/home/DOMAIN/public_html --allow-root) --force --path=/home/DOMAIN/public_html --allow-root

Prevent future bloat by limiting revisions in wp-config.php:

define( 'WP_POST_REVISIONS', 5 );

Clean Transients

Expired transients accumulate in the wp_options table:

sudo wp transient delete --expired --path=/home/DOMAIN/public_html --allow-root
sudo wp transient delete --all --path=/home/DOMAIN/public_html --allow-root

Optimize Tables

sudo wp db optimize --path=/home/DOMAIN/public_html --allow-root

Or via MySQL directly:

sudo mysqlcheck -o YOUR_DATABASE

Clean wp_options Autoload

The wp_options table loads all autoload=yes rows on every page load. Find large autoloaded entries:

sudo wp db query "SELECT option_name, LENGTH(option_value) AS size FROM wp_options WHERE autoload='yes' ORDER BY size DESC LIMIT 20;" --path=/home/DOMAIN/public_html --allow-root

Disable autoload on entries that do not need it:

sudo wp db query "UPDATE wp_options SET autoload='no' WHERE option_name='some_large_option';" --path=/home/DOMAIN/public_html --allow-root

Recommended Plugins

• WP-Sweep — safely cleans revisions, drafts, spam, transients, and orphaned data
• Advanced Database Cleaner — finds leftover tables from uninstalled plugins

Automate Cleanup

Schedule a weekly cron job to clean transients and optimize tables:

0 3 * * 0 sudo wp transient delete --expired --path=/home/DOMAIN/public_html --allow-root && sudo wp db optimize --path=/home/DOMAIN/public_html --allow-root