WordPress Database Optimization: Speed and Cleanliness

WordPress builds up clutter in the database: post revision drafts, transient cache, plugin logs, orphaned meta rows. The database size can grow from 10 MB to 1 GB with no visible reason — and that slows down the whole site, because every WP_Query call scans the bloated wp_posts and wp_options tables.

What clutters the WordPress database

Cleaning up with WP-CLI

# Delete all post revisions
wp post delete $(wp post list --post_type=revision --format=ids) --force

# Delete expired transients
wp transient delete --expired

# Delete all transients
wp transient delete --all

# Optimize every table
wp db optimize

Limit revisions in wp-config.php

// Keep at most 3 revisions per post
define('WP_POST_REVISIONS', 3);

// Disable revisions entirely
define('WP_POST_REVISIONS', false);

// Autosave every 120 seconds instead of 60
define('AUTOSAVE_INTERVAL', 120);

Problematic autoload options

Every WordPress page request loads all options with autoload='yes' into memory in a single query. If the total autoload size passes 1 MB, every hit to the site spends time deserializing clutter that nothing reads.

# Find the largest autoload options
wp db query "SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload='yes'
ORDER BY size DESC
LIMIT 20;"

# Total autoload weight -- the figure worth watching
wp db query "SELECT SUM(LENGTH(option_value)) FROM wp_options WHERE autoload='yes';"

Common cleanup errors explained

• Error establishing a database connection right after a bulk DELETE on revisions — the wp_posts table is locked by a long MyISAM transaction; switch the engine to InnoDB, which locks only the affected rows.
• WSOD (white screen) right after deleting transients — a plugin expected an option that the cleanup script removed along with the clutter; restore from backup and delete transients selectively by the plugin's prefix.
• Table './wp/wp_options' is marked as crashed — a MyISAM table got corrupted during a concurrent write while cleaning; fix it with wp db repair or REPAIR TABLE wp_options;.
• The site did not get faster after wp db optimize — the command defragments tables but does not touch autoload; if the very first load of options is what drags, look for the cause with the autoload sum query above.
• Lock wait timeout exceeded; try restarting transaction — the cleanup plugin and another plugin's cron job write to the same table at once; run bulk operations manually outside the active cron window instead of through web-cron under live traffic.

Plugins for database optimization

• WP-Optimize — scheduled automatic cleanup, including spam comment removal and abandoned tables left by old plugins.
• Advanced Database Cleaner — detailed clutter statistics broken down by table with a report export.
• Sweep — a simple, safe cleaner with no aggressive defaults.

Comparing cleanup methods

When database optimization will not help

Sometimes the database looks healthy but the site is still slow — the cause is not clutter:

• External requests are slow (fonts, social widgets, third-party APIs) — they never show up in MySQL queries; check the Network tab in the browser instead.
• The server is short on PHP memory or CPU — wp db optimize will not raise memory_limit or speed up template rendering.
• There is no page cache — without it WordPress runs the full set of queries on every visit, even against a perfectly optimized database; a full breakdown of speed factors is in the article on WordPress speed optimization.

How to verify the result

# Database size before and after -- compare these two numbers
wp db size --size_format=mb

# Homepage response time: measure before and after cleanup
curl -o /dev/null -s -w 'time_total: %{time_total}s\n' https://example.com/

# Confirm the site loads with no PHP errors
wp eval 'echo "OK: " . get_bloginfo("name");'

If the database size dropped and the homepage response time noticeably improved, the optimization worked. Repeat a one-off cleanup on a schedule every 1-2 weeks, or the clutter will build up again.