Database Optimization
Remove database bloat and optimize table structure to speed up every query WordPress executes.
Metrics
This technique can move up to 67% of LCP's share of the Lighthouse score.
This technique does not move TBT.
This technique does not move CLS.
This technique can move up to 67% of FCP's share of the Lighthouse score.
This technique can move up to 33% of SI's share of the Lighthouse score.
This technique can move up to 67% of TTFB's share of the Lighthouse score.
Overview
Over time, WordPress databases accumulate significant bloat: thousands of post revisions for every article ever edited, auto-draft and trashed posts, orphaned post meta from deleted plugins, expired transients clogging wp_options, and spam comments. Each of these adds rows to tables that every query must scan or sort through. Additionally, tables created by early WordPress versions or certain plugins use the MyISAM storage engine, which lacks row-level locking and performs poorly under concurrent write load. Cleaning and optimizing the database reduces query execution time across the board.
Impact grade, A+ to D: how much of the Lighthouse score this technique can move. Unaffected: TBT, CLS.
The Problem
A WordPress site that has been running for several years commonly has 50,000+ post revisions, 10,000+ orphaned meta rows, and an autoload section of wp_options exceeding 5 MB. The autoload section is fetched in its entirety on every single page request during PHP bootstrap — before any template logic runs. Every row in that set is deserialized and loaded into memory regardless of whether it is needed. This hidden overhead alone can add 100–500 ms to TTFB on shared hosting.
Performance Impact
LCP
Indirect
faster server response time carries through the entire waterfall — earlier HTML means earlier LCP
FCP
50–400ms
fewer rows in hot tables means faster query execution and less PHP bootstrap time
SI
Indirect
faster PHP execution means content appears in the viewport sooner
TTFB
100–500ms
autoload bloat reduction alone can cut server response time significantly on large sites
Risk: Medium
deleting revisions and meta is irreversible — take a database backup before bulk cleanup
How it works
01
Remove post revisions
WordPress saves a new revision every time a post is updated. A post edited 200 times has 200 revision rows in wp_posts, each with its own set of postmeta rows. Delete old revisions via WP-CLI or a cleanup plugin, and set
define('WP_POST_REVISIONS', 5);in wp-config.php to cap future revision accumulation.02
Clean expired transients and orphaned options
Expired transients remain in wp_options until explicitly deleted. Deactivated plugins often leave their option rows behind. Run
wp transient delete --expired --alland audit wp_options with the Autoload Analyzer (WP Hive) to identify orphaned rows safe to delete.03
Optimize table overhead
MySQL marks deleted rows as free space rather than releasing it immediately, causing table fragmentation.
OPTIMIZE TABLE wp_posts, wp_postmeta, wp_optionsreclaims that space and rebuilds indexes — reducing disk I/O for subsequent queries. Safe to run on any table.04
Convert MyISAM tables to InnoDB
InnoDB supports row-level locking (vs MyISAM’s table-level locking), crash recovery, and foreign keys. Converting is a one-time operation:
ALTER TABLE wp_options ENGINE=InnoDB;. Under concurrent traffic — multiple users posting comments or purchasing simultaneously — InnoDB prevents the write-lock bottleneck that MyISAM creates.
Code Example
# Check current autoload size (anything above 1 MB warrants cleanup)
wp db query "SELECT ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS autoload_mb FROM wp_options WHERE autoload='yes'" --allow-root<?php
# Limit post revisions going forward
define('WP_POST_REVISIONS', 5); // in wp-config.php
# Delete all but 5 most recent revisions per post (WP-CLI)
wp post delete $(wp post list --post_type=revision --format=ids) --force --allow-root
# Delete expired transients
wp transient delete --expired --all --allow-root
# Optimize key tables
wp db query "OPTIMIZE TABLE wp_posts, wp_postmeta, wp_options, wp_comments" --allow-rootAlways take a full database backup before running bulk delete operations. Test on a staging environment first.
Limitations
- Deleting post revisions is permanent — if you need to revert an article to a 6-month-old version after cleanup, that revision no longer exists.
- OPTIMIZE TABLE acquires a table lock for the duration of the operation on MyISAM tables. Run it during low-traffic hours on production.
- Database optimization is a maintenance task, not a one-time fix — schedule it quarterly or after major content migrations to prevent bloat from returning.
Common Mistakes
Running OPTIMIZE TABLE on large InnoDB tables during peak traffic
On InnoDB, OPTIMIZE TABLE rebuilds the table, which can take minutes on tables with millions of rows and briefly increases disk I/O. Use pt-online-schema-change (Percona Toolkit) for zero-downtime table rebuilds on large production databases.
Deleting all autoloaded options indiscriminately
Many autoloaded options are required by WordPress core or active plugins. Only remove options belonging to plugins that are no longer active. Use SELECT to inspect option names before deleting.