WordPress Database Optimization: OPTIMIZE TABLE, Repair, WP-CLI

Every WordPress database accumulates dead weight: orphaned meta rows, stale transients, thousands of post revisions, and table fragmentation that quietly degrades query speed. OPTIMIZE TABLE is the surgical tool that reclaims that space – but knowing when to use it, how InnoDB handles it differently from MyISAM, and how to automate the whole process separates a well-tuned production site from one that gradually slows to a crawl.

---

Why Database Maintenance Actually Matters

WordPress stores everything in MySQL: post content, metadata, user data, options, term relationships, and transient cache. After months of activity, that data develops several forms of rot that OPTIMIZE TABLE and related maintenance commands exist to address.

Table Fragmentation

Every time MySQL deletes a row or updates it to a larger value, it leaves gaps in the data file. New rows get written into those gaps when they fit, but leftover space just sits there as fragmentation. The data_free column in information_schema.tables shows you exactly how much wasted space each table carries. On a site with heavy post editing and deletion, wp_posts and wp_postmeta commonly show hundreds of megabytes of fragmented space.

Autoloaded Options Bloat

Every WordPress page load queries wp_options for all rows where autoload = 'yes'. Plugins that store large serialized arrays with autoload enabled force MySQL to read and parse that data on every single request. A poorly configured site can easily have 2-5MB of autoloaded options, which means 2-5MB of data deserialized on every page load before any content even renders.

Dead Data Accumulation

Post revisions accumulate silently. A post edited 200 times has 200 revision records in wp_posts, each with its own wp_postmeta rows. Spam comments fill wp_comments. Plugins that use WordPress transients without expiry times leave permanent rows. Orphaned meta rows – postmeta with no corresponding post – pile up whenever posts are force-deleted without proper cleanup hooks.

On a production site with 3 years of editing history, InnoDB fragmentation in wp_postmeta alone can account for 40-60% of the table’s allocated disk space.

---

OPTIMIZE TABLE: What the Command Actually Does

OPTIMIZE TABLE is a MySQL DDL statement that reorganizes the physical storage of table data, rebuilds table indexes, and updates table statistics. The exact operation differs based on the storage engine, which has major implications for how you schedule maintenance on modern WordPress installations.

The Syntax

The command itself is simple. The example below shows the basic form, querying fragmentation info, and targeting all tables dynamically:

You can run these statements directly in phpMyAdmin’s SQL tab, from the MySQL CLI, or via WP-CLI’s wp db query command.

---

InnoDB vs MyISAM: Completely Different Defragmentation Behavior

This is where most documentation gets it wrong – or glosses over the critical difference. WordPress installations created after 2018 use InnoDB for all tables by default. Older installs may still run MyISAM. The two engines handle OPTIMIZE TABLE in fundamentally different ways, with very different performance and locking implications.

MyISAM: Traditional Defragmentation

For MyISAM tables, OPTIMIZE TABLE does exactly what you’d expect: it rewrites the .MYD data file to pack all rows together, rebuilds the .MYI index file, and updates key statistics. The entire table is locked for reads and writes during the operation. On a large wp_posts table this can take 30-60 seconds, during which queries against that table queue up or time out.

InnoDB: ALTER TABLE Under the Hood

MySQL remaps OPTIMIZE TABLE to ALTER TABLE ... FORCE for InnoDB tables. This rebuilds the clustered index (the tablespace .ibd file) from scratch, effectively defragmenting both the data and all secondary indexes. Modern MySQL (5.6+) and MariaDB run this as an online DDL operation – the table remains readable and writable during most of the rebuild, with only a brief metadata lock at the start and end.

The catch: InnoDB’s tablespace file does not shrink automatically after the rebuild. MySQL reclaims fragmented pages internally but the .ibd file stays at its current size until a full rebuild. To reclaim disk space, you need innodb_file_per_table = ON (the default since MySQL 5.6), which stores each table in its own .ibd file that CAN shrink after an OPTIMIZE.

Behavior	MyISAM	InnoDB
OPTIMIZE maps to	Native defrag + repack	ALTER TABLE … FORCE
Table locking	Full read/write lock	Online DDL (brief metadata lock only)
Disk space reclaimed	Yes, immediately	Yes, if innodb_file_per_table = ON
Index rebuild	Yes	Yes (full clustered index rebuild)
Best time to run	Low-traffic window	Can run during business hours
Risk level	Higher (full lock)	Lower (online operation)

To check which engine each of your tables uses and how much fragmentation they carry:

A fragmentation percentage above 20% on any table is generally worth addressing. Tables under 10MB with low fragmentation can be skipped entirely – the overhead of optimizing them outweighs the benefit.

---

Using WP_ALLOW_REPAIR for Corrupted Tables

WordPress ships a built-in database repair and optimization tool at /wp-admin/maint/repair.php. It’s disabled by default for security reasons – you have to explicitly enable it in wp-config.php and disable it again after use.

This tool is most useful when you’re dealing with corrupted MyISAM tables (InnoDB corruption requires different recovery approaches), or when you want a quick optimization pass without setting up WP-CLI or phpMyAdmin access.

The repair page offers two operations: “Repair Database” runs REPAIR TABLE on each table to fix corruption without defragmenting, and “Repair and Optimize Database” runs both REPAIR and OPTIMIZE TABLE on every table. The second option is what you want for routine maintenance.

WP_ALLOW_REPAIR bypasses login entirely. Anyone who knows the URL can trigger a database repair while it’s enabled. Set it, run it, remove it – do not leave it active.

---

phpMyAdmin: Manual Maintenance Operations

If you have phpMyAdmin access (most shared hosts provide it via cPanel), you have a point-and-click interface for all the maintenance operations covered here. The relevant section is under the Operations tab when you select a database or individual table.

Optimize All Tables at Once

• Open phpMyAdmin and select your WordPress database from the left sidebar
• In the main table list view, scroll to the bottom and look for “Check all” or select specific tables with checkboxes
• Click “Check all” then use the “With selected:” dropdown – choose “Optimize table”
• phpMyAdmin runs OPTIMIZE TABLE on each selected table and reports results in a table showing the status (OK, Table is already up to date, or specific repair actions taken)

Reading the Results

For InnoDB tables you’ll typically see Table does not support optimize, doing recreate + analyze instead followed by OK. This is normal – it’s MySQL confirming that it ran the ALTER TABLE … FORCE operation instead of a native optimize. For MyISAM tables you’ll see straightforward OK status after defragmentation.

Checking Table Status

phpMyAdmin’s “Structure” view shows the Data and Index size for each table alongside a “Fragmentation” indicator. The Operations tab on any individual table shows the same data plus the “Table maintenance” section with Analyze, Check, Optimize, Repair, and Flush buttons. Use Analyze when you want to update index statistics without rebuilding the table – this is much faster and suitable for daily or weekly runs.

---

WP-CLI: The Right Tool for Production Maintenance

WP-CLI is the correct tool for database maintenance on any site where you have SSH access. It’s faster than phpMyAdmin, scriptable, produces consistent output you can log, and doesn’t require a browser session. The wp db command group covers everything from health checks to optimization to export/import.

The critical habit here is running wp db export before any maintenance operation. wp db optimize is low risk on modern InnoDB tables, but wp db repair can make schema changes – you want a backup before that runs. On managed hosts like Kinsta, WP Engine, or Cloudways, their platform may prevent direct MySQL access but WP-CLI is almost always available via SSH.

Scheduling WP-CLI via Server Cron

For ongoing maintenance, add a server-level cron job rather than relying on WP-Cron. WP-Cron only fires when someone visits the site, which means scheduled maintenance on low-traffic hours may not run at all. A real cron job runs on schedule regardless of traffic:

If you prefer a WP-Cron approach (useful for shared hosting where you lack SSH), the PHP implementation above handles the same operations through WordPress’s scheduling system. It also cleans expired transients as part of the same maintenance pass, which reduces the number of separate cron jobs you need to manage.

---

Plugin Options: WP-Optimize and Advanced Database Cleaner

If WP-CLI isn’t an option and you want something beyond phpMyAdmin’s manual workflow, two plugins are worth knowing: WP-Optimize and Advanced Database Cleaner. Both automate database cleanup and optimization – but they have different strengths and you should understand what they’re actually doing before scheduling them on a production site.

WP-Optimize

WP-Optimize (free tier from WeAreSpark) handles the full cleanup pipeline in one interface: remove post revisions, clean spam and trashed comments, remove expired transients, remove orphaned postmeta, and run OPTIMIZE TABLE on all tables. The free version provides one-click cleanup with configurable settings for each category. The premium tier adds scheduled automatic runs and multisite support.

Key settings to configure before running: set a maximum revision age (keep last 5 revisions per post rather than deleting all of them), exclude specific tables from optimization if you’re running InnoDB with large tables that you know don’t fragment heavily, and review what it considers “orphaned” data before running – some plugins legitimately store postmeta rows that aren’t linked to any post in wp_posts.

Advanced Database Cleaner

Advanced Database Cleaner (by Younes JFR) takes a more surgical approach. It shows you a detailed breakdown of each category of junk data – orphan tables, orphaned options, orphaned transients, specific custom post type revisions – and lets you inspect individual items before deleting. This is more useful when you need to investigate what’s eating space rather than just run a blanket cleanup. The orphan table detection is particularly useful on sites that have had plugins installed and uninstalled over the years, since deactivated plugins often leave their custom tables behind.

When NOT to Use Plugins

Both plugins use standard MySQL queries and WP APIs internally – they’re not doing anything you couldn’t do yourself with WP-CLI or SQL. On high-traffic production sites with large databases (5GB+), running OPTIMIZE TABLE through a web request can hit PHP execution timeouts. WP-CLI avoids this because it runs outside the PHP-FPM execution timeout. For databases above roughly 2GB, use WP-CLI or direct MySQL access rather than these plugins.

---

Monitoring Database Health Over Time

Running OPTIMIZE TABLE once and forgetting about it is not a maintenance strategy – it’s a one-time intervention. Actual database health requires regular monitoring to catch problems before they affect site performance. The SQL queries below give you the key metrics to track:

Run these queries monthly (or quarterly on lower-traffic sites) and compare the results over time. The numbers to watch: autoloaded options total size (alert if it crosses 800KB), fragmentation percentage on any table above 30%, and revision count growth (a rapidly growing revision count on a small site suggests the post revisions limit hasn’t been configured).

Limiting Post Revisions to Prevent Future Bloat

The most effective long-term maintenance strategy isn’t cleaning up after yourself – it’s preventing the accumulation in the first place. Add this to wp-config.php to cap post revisions:

Use define( 'WP_POST_REVISIONS', 5 ); to keep only the 5 most recent revisions per post. For more control over revision management, the dedicated guide on how to limit, disable, and clean up post revisions covers additional strategies including per-post-type limits. This alone can prevent hundreds of thousands of orphaned revision rows from accumulating over a year of active content editing. Set it to false to disable revisions entirely (not recommended unless you’re absolutely sure you never need revision history).

Query Cache and Object Cache

OPTIMIZE TABLE improves disk storage efficiency, but if the same expensive queries keep running, you’re patching a symptom rather than the cause. Persistent object caching (via Redis or Memcached using the Redis Object Cache plugin) eliminates repeat database hits entirely for cached objects. Sites seeing high database CPU after optimization should audit slow query logs before running another OPTIMIZE – the bottleneck may be a missing index or an N+1 query pattern, not fragmentation.

---

Common Issues and Troubleshooting

OPTIMIZE TABLE Times Out

If a table optimization times out via phpMyAdmin or a plugin, the table may still be in an inconsistent state during the operation. Run it via WP-CLI or MySQL CLI where execution timeouts don’t apply. For very large InnoDB tables, ALTER TABLE wp_posts ENGINE=InnoDB is equivalent to OPTIMIZE TABLE and runs as an online DDL operation that won’t timeout at the MySQL level.

InnoDB Message: “Table does not support optimize”

This is not an error. MySQL is informing you that for InnoDB tables it substitutes a recreate + analyze operation for the native OPTIMIZE TABLE behavior. The table is being rebuilt and defragmented. The final “OK” status confirms it completed successfully.

Disk Space Not Recovered After OPTIMIZE

For InnoDB tables, disk space recovery requires innodb_file_per_table = ON (check with SHOW VARIABLES LIKE 'innodb_file_per_table'). If it’s OFF, all InnoDB tables share a single ibdata1 file that never shrinks. Enabling per-table files requires rebuilding each table – consult your hosting provider before changing this on a production server, as it affects all databases on the MySQL instance.

Site Goes Down During Optimization on MyISAM

MyISAM tables lock fully during OPTIMIZE TABLE. On a large wp_posts table, this can take over a minute, during which every query against that table queues. Always run MyISAM optimization during verified low-traffic periods. For any new installs or sites you control the configuration of, migrating to InnoDB eliminates this risk entirely since InnoDB optimization runs online.

Autoloaded Options Won’t Decrease

Cleaning up large autoloaded options requires identifying which plugin or theme is writing them and either reconfiguring it to not use autoload or deactivating it. Running OPTIMIZE TABLE after a manual options cleanup consolidates the freed space but doesn’t prevent the data from being re-written on the next page load if the plugin is still active. Use the health check query above to identify the offending option names, then trace them back to their plugin via a code search for that option name in your plugins directory.

---

Building a Monthly Maintenance Routine

A practical monthly routine for a production WordPress site covers these steps in order:

1. Backup first – wp db export before any destructive operation
2. Check fragmentation levels – query information_schema.tables for current data_free values
3. Clean dead data – delete post revisions beyond the last 5, expired transients, spam/trashed comments, orphaned postmeta
4. Optimize tables with fragmentation above 20% – skip tables that are already efficient
5. Check autoloaded options size – alert if total exceeds 800KB, investigate if above 1MB
6. Run ANALYZE TABLE on frequently queried tables – this updates index statistics without the overhead of a full rebuild
7. Review slow query log if available – fragmentation is rarely the cause of the slowest queries

The WP-CLI commands and PHP cron implementation in the code examples above automate steps 3-4. Steps 1, 5, and 7 are deliberate manual checkpoints – the kind of review that catches issues before they escalate to outages.

---

Related Tweaks Worth Combining

Database optimization works best as part of a broader performance strategy. If you’re already running monthly OPTIMIZE TABLE passes, these related tweaks compound the improvement:

• Add a persistent object cache (Redis Object Cache plugin) to eliminate repeat database hits entirely for cached objects and options
• Enable WP_POST_REVISIONS cap in wp-config.php to prevent revision accumulation going forward
• Audit slow query logs with the Query Monitor plugin, and consider adding custom database indexes on wp_postmeta to speed up meta-heavy queries that optimization alone cannot fix
• Configure a database backup schedule that runs before your optimization cron job, not after

Database maintenance is one layer of a complete WordPress performance stack. The articles below cover the adjacent problems – specifically slow query identification and image optimization – that often surface once the basic database hygiene is in place.

---

database cleanup database maintenance database optimization WP-CLI

Last modified: March 26, 2026