wp db optimize

Overview

Defragment and compact WordPress database tables to reclaim disk space from deleted rows and improve query performance. Run after bulk deletions, large imports, or as part of regular maintenance.

What It Does

wp db optimize runs mysqlcheck --optimize on all WordPress tables. For MyISAM tables, this defragments the data file. For InnoDB tables, it performs an ALTER TABLE ... ENGINE=InnoDB rebuild (equivalent to OPTIMIZE TABLE), which:

• Reclaims space from deleted/updated rows
• Rebuilds indexes
• Updates table statistics for the query optimizer

Syntax

wp db optimize [--dbuser=<value>] [--dbpass=<value>]

Options & Flags

Flag	Description
--dbuser=VALUE	Override DB user
--dbpass=VALUE	Override DB password

Basic Usage

Optimize all tables

wp db optimize

Output:

Success: Database optimized.

When to Run

Trigger	Recommended Action
After deleting many posts	wp db optimize
After purging spam comments	wp db optimize
After bulk plugin/theme cleanup	wp db optimize
After wp db import on large dump	wp db optimize
Monthly scheduled maintenance	wp db optimize

Real-World Scenarios

Scenario 1: Post-cleanup optimization

# Delete spam comments
wp comment delete $(wp comment list --status=spam --format=ids) --force

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

# Optimize after bulk deletion
wp db optimize
wp db size --size_format=mb

Scenario 2: Monthly maintenance script

#!/bin/bash
echo "=== Monthly DB Maintenance: $(date) ==="

# Delete transients
wp transient delete --expired

# Delete spam comments
wp comment delete $(wp comment list --status=spam --format=ids 2>/dev/null) --force 2>/dev/null || true

# Optimize
wp db optimize

# Report size
echo "DB size after optimization:"
wp db size --size_format=mb

Scenario 3: Size comparison before and after

echo "Before:"
wp db size --size_format=mb

wp db optimize

echo "After:"
wp db size --size_format=mb

Best Practices

1. Never skip wp db optimize after bulk deletions — deleted rows leave fragmented space.
2. Run during low-traffic hours — InnoDB optimization rebuilds tables and can be slow on large tables.
3. Combine with transient cleanup — transient delete --expired reduces wp_options bloat before optimizing.
4. Schedule monthly via cron as part of regular DB maintenance.

Troubleshooting

Problem	Cause	Fix
InnoDB table shows note: recreate + analyze instead	Expected — InnoDB doesn't defrag the same as MyISAM	This is normal; space is still reclaimed
Command is slow	Large tables being rebuilt	Run during off-peak hours
Access denied	DB user lacks LOCK TABLES or ALTER privilege	Use root or grant privileges

Quick Reference

wp db optimize                          # Optimize all tables
wp db size --size_format=mb            # Check size before/after
wp db repair                            # Repair before optimizing (if needed)

Next Steps

• wp db repair — repair corrupted tables before optimizing.
• wp db size — verify size savings after optimization.
• wp transient delete --expired — clean transients before optimizing.