Speed & performance · Guide
How to Optimize the WordPress Database
Short answer
Optimize the WordPress database by backing it up, measuring table sizes, then cleaning the four sources of bloat in order: autoloaded options, post revisions, expired transients, and spam, trash and orphaned rows. Finish with wp db optimize, and repeat the routine every three months.
Key takeaways
- Site Health flags autoloaded options as critical past 800,000 bytes.
- WordPress 6.6 stops autoloading new options larger than 150,000 bytes.
- Transients without an expiration are autoloaded; ones with an expiration are not.
- WP_POST_REVISIONS defaults to true, which means unlimited revisions.
- Run wp db optimize after deleting rows, or the space is not reclaimed.
What lives in the WordPress database?
The WordPress database holds every post, page, comment, setting and user, plus the temporary data plugins create.
The main tables are wp_posts for content, wp_postmeta for custom fields, wp_options for settings, wp_users and wp_usermeta for accounts, and wp_comments and wp_commentmeta for discussion. Plugins add their own tables on top of these.
The database grows with use. Every saved draft, every revision, every spam comment and every temporary cache entry adds rows, and rows that nothing cleans up stay there for years.
A large database is not a problem by itself. It becomes a problem when the part loaded on every request grows, or when tables fill with rows no page ever reads.
What makes a WordPress database slow?
Four kinds of bloat cause most database slowness: autoloaded options, revisions, transients and orphaned rows.
Autoloaded options are settings WordPress loads into memory on every request, whether the page needs them or not. When plugins store large data there, every visit pays the cost.
Revisions are full copies of a post saved with each edit. A post edited 50 times carries 50 copies in wp_posts, and the count grows with every save.
Transients are temporary values plugins store with a lifetime. A transient set without an expiration never expires, and it stays in the options table until someone removes it.
Orphaned rows are leftovers from deleted content. A deleted post can leave its metadata behind, and a removed plugin can leave its tables and settings in place.
The fix follows one order: back up, measure, then clean each source in turn. Cleaning without measuring means you cannot tell which source was the problem.
How do you back up and measure the database first?
Never run a delete query before a backup, because most cleanup cannot be undone.
- Open a terminal in the WordPress root folder and run
wp db export backup-before-cleanup.sql. - Check the backup file exists and has a sensible size, then move a copy outside the public web folder.
- Run
wp db size --tables --human-readableto list every table with its size. - Note the three largest tables and the total size, so you can compare them after the cleanup.
Developer.WordPress.org documents wp db size with the --tables flag for per-table sizes and --human-readable for readable numbers. The same reference lists wp db export for exports and wp db optimize for optimizing tables.
If you have no command line access, use phpMyAdmin from your hosting panel instead. Open the database, click the Size column to sort tables, and use the Export tab for the backup.
Measure the autoload total as well. Run wp option list --autoload=on --format=total_bytes to print the total size of autoloaded options in bytes. Developer.WordPress.org documents this exact command in its examples.
A total over 800,000 bytes is worth fixing, because WordPress flags it as a problem, as the next section explains.
How do you fix autoloaded options?
Autoloaded options are the first thing to check, because they load on every single request.
WordPress loads all autoloaded options in one query at the start of each request. The WordPress Site Health reference states that the check turns critical when the total passes 800,000 bytes, which is the default limit set by the site_status_autoloaded_options_size_limit filter.
Open Tools, then Site Health, and look for the warning “Autoloaded options could affect performance”. If it is there, the total has passed 800,000 bytes.
List the largest options with this query in phpMyAdmin or wp db cli, replacing wp_ with your own table prefix:
SELECT option_name, ROUND(LENGTH(option_value) / 1024, 1) AS size_kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 20;
WordPress 6.6 changed how autoload works. Make WordPress Core explains that new options use the values on, off, auto, auto-on and auto-off, and that an option saved without an explicit choice is not autoloaded when its value is larger than 150,000 bytes.
Work through the top 20 list one row at a time. Find which plugin owns each option from its name, then turn off autoload when the data is rarely needed, or delete the row when the plugin is gone.
Turn off autoload with wp option set-autoload option_name off, or delete a dead plugin's row with wp option delete option_name. Never delete an option you cannot identify, and never delete core options such as siteurl or home.
How do you limit post revisions?
Revisions are useful history, but unlimited history is a slow leak in wp_posts.
WordPress saves a copy of a post each time you save it. The WordPress Codex states that revisions are on by default, with WP_POST_REVISIONS defaulting to true, and that setting it to false disables the feature.
Limit revisions in wp-config.php by adding this line above the “stop editing” comment:
define( 'WP_POST_REVISIONS', 5 );
The number keeps that many revisions per post. Five is a good balance: enough history to recover a mistake, small enough to stop the table growing without limit.
Change the autosave interval in the same file when drafts pile up. The Codex notes that the default autosave interval is 60 seconds, and you can raise it with define( 'AUTOSAVE_INTERVAL', 300 ); for a five-minute gap.
The limit only stops new revisions. Old ones stay until you remove them, so run a cleanup after setting the limit.
WP-Optimize removes old revisions in one pass. WordPress.org states that it cleans post revisions, auto draft posts, trashed posts, spam and trashed comments, and that clean-ups can run on a daily, weekly, fortnightly or monthly schedule.
How do you clear expired transients?
Transients are meant to be temporary, but the ones without an expiration never leave on their own.
A transient is a value a plugin stores for a set time, such as an API answer cached for an hour. Developer.WordPress.org states plainly that transients that never expire are autoloaded, while transients with an expiration time are not autoloaded.
That single detail is why transients matter for speed. A transient without an expiration loads on every request, forever, even when no page uses it.
Delete expired transients with one WP-CLI command:
wp transient delete --expired
Developer.WordPress.org documents this command, plus wp transient delete --all for removing every transient at once. Start with --expired, because it only removes transients past their lifetime.
WordPress rebuilds any transient it still needs on the next request. A deleted transient costs one fresh API call or query, then the cache fills again.
If the same transients return within days, find the plugin that creates them. A plugin that stores large API answers without an expiration is the real problem, and the fix is a setting in that plugin or a replacement.
How do you remove spam, trash and orphaned data?
Spam comments, trashed posts and orphaned metadata are the easiest wins, because nothing on the site needs them.
Empty the spam and trash queues from the dashboard first. Open Comments, then Spam, and delete the whole queue, then open Posts, then Trash, and do the same.
Delete trashed posts from the database when the dashboard is too slow for a large queue. WP-Optimize covers trashed posts and spam comments in its cleanup list, and it can run the job on a schedule so the queues never build up again.
Remove orphaned post metadata next. These are rows in wp_postmeta whose post no longer exists, left behind when posts were deleted directly from the database or by a faulty plugin.
A cleanup plugin can find them, but check its list before you approve the delete. Metadata rows with no parent post are safe to remove, while rows tied to a live post must stay.
Do not delete tables from plugins you still use. A table that looks dead may belong to a plugin that is only inactive for a season, so confirm the plugin is gone before you drop its tables.
How do you optimize the tables?
Deleting rows frees space inside the tables, but the tables themselves need rebuilding to give that space back.
| Command | What it does | When to run it |
|---|---|---|
wp db check | Checks tables for errors | Before any cleanup |
wp db export | Exports the database to a file | Before any delete |
wp db size --tables | Lists each table with its size | Before and after cleanup |
wp transient delete --expired | Removes expired transients | During cleanup |
wp db optimize | Optimizes the database tables | After deleting rows |
wp db size --size_format=mb | Prints the total size in megabytes | After cleanup, to confirm |
Run wp db check first to catch damaged tables. A table that reports errors needs repair before optimization, and our guide to fix database tables that need repair covers the repair steps.
Run wp db optimize after the deletes. Developer.WordPress.org lists wp db optimize as the command that optimizes the database, and it rebuilds tables so the freed space is reclaimed.
Run the size check again after optimizing. The total should be smaller, and the table that was largest should have shrunk. If nothing changed, the bloat was in a table you did not clean.
Optimization locks tables briefly while it runs, so do it during a quiet hour. On a large site, optimize one table at a time rather than the whole database at once.
How do you keep the database lean?
A quarterly routine keeps the database from growing back, and it takes about 30 minutes each time.
Set the revision limit in wp-config.php once, and leave it there. A limit of 5 revisions per post stops the largest source of growth without losing useful history.
Schedule a cleanup plugin to run monthly. WP-Optimize can run its clean-ups on a daily, weekly, fortnightly or monthly schedule, so pick monthly and let it clear revisions, spam and transients.
Watch the autoload total after plugin changes. Every new plugin adds options, so re-run wp option list --autoload=on --format=total_bytes after installing one, and remove the plugin's leftovers when you uninstall it.
Keep an object cache in mind for busy sites. An object cache such as Redis stores transients and options in memory instead of the database, and our guide to Redis versus Memcached for WordPress compares the two options.
If the site cannot connect to the database at all, the problem is different from bloat. Our guide to what causes the error establishing a database connection explains the causes.
The repair itself is a separate job. Our guide to fix the error establishing a database connection walks through it step by step.
Frequently asked questions
How do I know if my autoloaded options are too large?
The autoload total is the size of the settings WordPress loads on every request. Run wp option list --autoload=on --format=total_bytes to see it. Site Health flags the total as critical when it passes 800,000 bytes.
How many post revisions should I keep?
Five is a good balance. It keeps enough history to recover a mistake while stopping the revisions table from growing without limit. Set it with define( 'WP_POST_REVISIONS', 5 ); in wp-config.php.
Is it safe to delete transients?
Yes, when you use --expired. That flag removes only transients past their lifetime, and WordPress rebuilds any it still needs on the next request. The --all flag also removes live transients, so use it only when you mean to clear everything.
Does deleting rows shrink the database right away?
No. Deleting rows frees space inside the tables, but the space is only reclaimed when the tables are rebuilt. Run wp db optimize after the deletes, then check the size again to confirm the shrink.
Can I delete any option I find in wp_options?
No, and you should not. Options such as siteurl and home are needed for the site to work at all. Delete only rows that belong to a plugin you removed, and turn off autoload rather than deleting when you are unsure.
How often should I optimize the database?
About 30 minutes every three months. Set the revision limit once, schedule a monthly cleanup in WP-Optimize, and re-check the autoload total after installing or removing a plugin.
Sources
- WP_Site_Health::get_test_autoloaded_options() Developer.WordPress.org
- Options API: Disabling autoload for large options Make WordPress Core
- set_transient() Developer.WordPress.org
- wp db size Developer.WordPress.org
- wp transient Developer.WordPress.org
- wp option list Developer.WordPress.org
- Editing wp-config.php WordPress Codex
- WP-Optimize WordPress.org
Is your product missing, or something out of date?
Tell us what to add or correct. We read every note and reply when we need a source. A message does not buy a ranking.
Thanks — we review requests within 3 business days.
