Database optimisation

Deleting rows from a database does not immediately shrink the file that holds them. The space is marked reusable but the table stays fragmented, and indexes can end up less efficient than when they were built. OPTIMIZE TABLE is the MySQL operation that rebuilds a table to fix both.

This feature runs it on every table that uses your WordPress table prefix, as part of a cleanup run. It changes no data – every row that existed before still exists afterwards. Only the physical arrangement on disk changes.

It pairs naturally with Database cleanup. Cleanup removes the rows, optimisation compacts what is left.

The tables that churn most are the ones that matter most. wp_options is read on every request. wp_postmeta grows and shrinks constantly as plugins write and remove data. After months of revisions being created and cleared, and transients expiring, those tables carry gaps that make sequential reads less efficient than they should be.

Optimising rebuilds them compactly. On a site with a long history the file size reduction alone can be substantial, which makes backups faster and migrations less painful – benefits that show up well beyond page load time.

On MyISAM tables, OPTIMIZE TABLE defragments the data file and sorts the index pages. On InnoDB, which is the default storage engine for modern WordPress, MySQL maps the command to an in-place table rebuild followed by an index rebuild and a statistics refresh.

That in-place rebuild is why timing matters. The operation is safe, but it is not instant on a large table and it holds locks while it runs. On a small site it completes in seconds; on a large store it can take considerably longer.