Every page view on a WordPress site starts with a conversation between PHP and the database. WordPress asks for settings, posts, menus, widgets and user data, and the database answers. On a fresh install that conversation is quick. A few years, a few hundred plugins tried and removed, and a busy WooCommerce store later, the same database can carry millions of rows that nobody needs, and every request pays a small tax to work around them.
That is why wordpress database optimization is worth doing properly, not just by clicking “clean everything” in a plugin. The goal is not a smaller number on a dashboard. It is faster queries, smaller backups and fewer surprises. This guide explains what actually lives in the database, how bloat builds up, how to measure before you touch anything, how to deal with autoloaded options, revisions, transients and WooCommerce logs, what table optimization really does, and how to keep the database lean without risking the site.
What Actually Lives in the WordPress Database
A standard WordPress install creates a small set of tables, usually with the wp_ prefix. wp_posts holds far more than blog posts: pages, revisions, menu items, media attachments, WooCommerce orders on older setups and any custom post type a plugin registers. wp_postmeta stores extra fields for each of those items, and it is often the largest table on the site because page builders, SEO plugins and stores write many rows per post.
The wp_options table is the site’s settings drawer. Core settings, theme options, widget configurations, plugin settings and cached data all live there. The Options API documentation explains how plugins add and read these values, and it is the reason the table grows: almost every plugin you have ever activated wrote something into it, and many never clean up when they are removed.
Then there are comments and their meta, users and their meta, and the three taxonomy tables that connect posts to categories and tags. Plugins add their own tables on top, especially security, analytics, form, redirect and ecommerce plugins. A store running WooCommerce also gets order tables, session data and the Action Scheduler tables that run background jobs.
None of this is a problem by itself. A large database is not automatically a slow one. A site with 50,000 well-indexed products can be faster than a brochure site with a 900 KB pile of autoloaded settings. What matters is which data WordPress has to read on every request, how many rows a query has to scan to find what it needs and how much dead data rides along in every backup and migration.
Keeping that distinction in mind changes how you approach cleanup. Instead of deleting whatever a tool lists, you focus on the few areas that actually slow pages down, and you leave the rest alone or remove it only when it is clearly abandoned. To do that, you first need to know where the weight comes from.
How a Database Gets Bloated
Bloat rarely comes from one mistake. It builds up quietly from normal use, and most of it falls into a handful of familiar patterns:
- Post revisions saved on every edit, sometimes hundreds per page on sites that use page builders.
- Autoloaded options left behind by plugins that were deleted years ago.
- Expired transients that were never cleaned because of a missing object cache or broken cron.
- Spam, trashed and unapproved comments waiting for someone to empty them.
- Orphaned postmeta pointing to posts that no longer exist.
- Logs from security, email, redirect and form plugins that keep every event forever.
- Action Scheduler history on WooCommerce sites, with completed and failed jobs piling up.
Each of these has a different cost, which is why treating them all the same is a mistake. The table below shows where each one usually hurts.
| Source of Bloat | Where It Lives | Main Impact | Safe to Bulk Delete? |
|---|---|---|---|
| Post revisions | wp_posts, wp_postmeta | Larger tables, slower admin queries | Yes, keep a few recent ones |
| Autoloaded options | wp_options | Slower every page, front end and admin | No, review one by one |
| Expired transients | wp_options | Bigger options table, sometimes autoloaded | Yes, expired ones only |
| Spam and trash | wp_comments, wp_posts | Larger tables and backups | Yes |
| Orphaned postmeta | wp_postmeta | Larger table, slower meta lookups | Yes, after a backup |
| Plugin logs | Custom tables | Large backups, slow plugin screens | Yes, within the plugin’s settings |
| Action Scheduler history | actionscheduler_ tables | Slow WooCommerce admin, huge tables | Completed and canceled, yes |
Notice the one row marked “no.” Autoloaded options are where most real speed gains hide and where careless cleanup breaks sites, so they get their own section below. Before any of that, though, you need numbers.
Measure and Back Up Before You Clean
Optimization without measurement is guesswork. Start by finding the size of each table. If you have shell access, WP-CLI’s db size command lists every table with `wp db size –tables –human-readable`, which instantly shows whether wp_options, wp_postmeta or a plugin table is the giant. Without shell access, phpMyAdmin or your host’s database tool shows the same sizes on the table list.
Next, check autoloaded data. WordPress 6.6 added a Site Health test that raises a critical issue called “Autoloaded options could affect performance” when the total passes 800 KB, as described in the WordPress core announcement for that release. If Site Health shows that warning, you have found your first target. Note the current page load time and Time to First Byte too, so you can compare after cleanup.
Then take a full backup of files and database, and confirm you can restore it. Every DELETE in this guide is permanent. Our guide on how to back up a WordPress site covers the full process, including manual database exports.
Finally, run anything bigger than a plugin’s standard cleanup on a staging copy first. A WordPress staging site lets you test queries, check that forms, checkout and page builder layouts still work, and only then repeat the change on live.
Autoloaded Options: The Hidden Weight on Every Page
Every option in wp_options has an autoload flag. When it is on, WordPress loads that option into memory on every single request, whether the page needs it or not. This is efficient for small, frequently used settings like the site URL or active plugins list. It becomes a problem when plugins autoload large blobs of data, such as cached API responses, builder data, statistics or settings for features you turned off years ago.
The trouble is that autoloaded data is invisible. It does not show up as a slow query in most tools because it is fetched in one query at the start of every request. A site with 3 MB of autoloaded options moves that 3 MB from the database to PHP on every page view, every admin screen, every AJAX call and every REST request. With a persistent object cache it lives in memory instead, but it still gets loaded and unpacked each time.
WordPress 6.6 changed how this works. New options can now be stored with values like “on,” “off” and “auto,” and WordPress stops autoloading any option larger than 150 KB unless a plugin explicitly asks for it. Older options keep their old “yes” and “no” values, though, so a site that has been around for years can still carry plenty of heavy autoloaded data. The table below explains the values you will see in the autoload column.
| Autoload Value | Meaning | Loaded on Every Request? |
|---|---|---|
| yes | Legacy value from before WordPress 6.6 | Yes |
| no | Legacy value from before WordPress 6.6 | No |
| on | Plugin explicitly asked to autoload | Yes |
| off | Plugin explicitly asked not to autoload | No |
| auto | WordPress decides, currently autoloaded | Yes |
| auto-on / auto-off | WordPress decided based on size | Yes / No |
To find the heaviest autoloaded options, run this query in phpMyAdmin or with `wp db query`, adjusting the table prefix if yours is not wp_:
“`sql
SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload IN (‘yes’, ‘on’, ‘auto’, ‘auto-on’)
ORDER BY bytes DESC
LIMIT 20;
“`
Look at the names, not just the sizes. An option prefixed with the name of a plugin you removed long ago is a strong candidate for deletion. An option belonging to an active plugin should usually be switched to “off” rather than deleted, and only after testing, because some plugins expect their settings to be in memory. Never touch core options such as siteurl, home, active_plugins, cron or rewrite_rules. If the cron option itself is huge, that points to a broken scheduled task, and the fix is the task, not the option.
This is the one area where we recommend a manual review over any one-click tool. Deleting the wrong option can reset a theme’s design or break a plugin’s license. Switching autoload off for the wrong option only costs performance. When in doubt, choose the reversible change and test on staging first.
Revisions, Trash, Spam and Expired Transients
With autoload under control, the routine cleanup is much simpler. Post revisions are useful, because they let you roll back a bad edit, but nobody needs 300 versions of the same page. Deleting old revisions and then limiting how many WordPress keeps going forward solves the problem for good. The setting lives in wp-config.php.
Transients are cached values with an expiry time, and the Transients API is clear that they can disappear at any time, so deleting them should not break anything. Without a persistent object cache they live in wp_options, and expired ones are not always removed. The safest approach is to delete only expired transients, for example with WP-CLI’s transient delete command using `wp transient delete –expired`. Deleting all transients is also safe in theory, but it forces every plugin to rebuild its cache at once, which can briefly slow a busy site.
The constants below, set in wp-config.php and documented in the official wp-config.php guide, keep these areas small without any plugin.
| Constant | Example | What It Does |
|---|---|---|
| WP_POST_REVISIONS | define( ‘WP_POST_REVISIONS’, 5 ); | Keeps at most 5 revisions per post |
| AUTOSAVE_INTERVAL | define( ‘AUTOSAVE_INTERVAL’, 120 ); | Autosaves every 120 seconds instead of 60 |
| EMPTY_TRASH_DAYS | define( ‘EMPTY_TRASH_DAYS’, 14 ); | Empties trash after 14 days instead of 30 |
| WP_ALLOW_REPAIR | define( ‘WP_ALLOW_REPAIR’, true ); | Unlocks the built-in repair screen, remove after use |
Avoid setting WP_POST_REVISIONS to false. A small number keeps the safety net while stopping the growth. Spam and trashed comments can simply be emptied from the Comments screen, and trashed posts from each post type’s trash view.
WooCommerce and Action Scheduler Tables
Stores deserve their own attention, because WooCommerce does much of its work in the background. Emails, webhooks, subscription renewals, stock syncs and many extensions use Action Scheduler, a job queue library described on the Action Scheduler site. Every job gets a row, and every job writes log entries.
By default, completed and canceled actions are purged after about a month. That cleanup relies on WordPress cron running reliably. When cron is broken, or when an extension creates thousands of failed jobs a day, the actions and logs tables can reach millions of rows. The symptoms are a slow WooCommerce admin, slow checkouts during peaks and backups that suddenly take hours.
You can see the queue under WooCommerce, then Status, then Scheduled Actions. A large pile of pending actions means the queue is not running. A large pile of failed actions points to a specific extension or integration that needs fixing. Clearing old completed and failed rows helps, but only fixing the cause stops them from coming back, which is a common item in WooCommerce maintenance.
Optimizing Tables and When Indexes Matter
Once the dead rows are gone, the space they used is not automatically returned. That is what OPTIMIZE TABLE is for. On modern WordPress databases, tables use the InnoDB engine, and the MariaDB OPTIMIZE TABLE documentation explains that for InnoDB the command rebuilds the table and updates index statistics. You may see a message saying the table does not support optimize and is doing “recreate + analyze” instead. That is normal and means it worked.
Two cautions apply. First, a rebuild needs temporary disk space roughly the size of the table, so check free space before optimizing a multi-gigabyte table. Second, depending on the server version and settings, a rebuild can briefly lock a table, so run it on large tables during quiet hours. WP-CLI’s `wp db optimize` command runs the optimization across all tables in one step, and the built-in repair screen does the same from the browser when WP_ALLOW_REPAIR is enabled.
Indexes are a different matter. They make lookups fast, and WordPress core tables already have sensible ones. Slow queries usually come from plugins searching meta values or running unbounded queries, not from missing core indexes. The Query Monitor plugin shows slow and duplicate queries per page along with the plugin that ran them, which tells you whether to fix a plugin, replace it or bring in a developer. Adding custom indexes can help, but it is a developer task best tested on staging, because indexes also slow down writes and can be lost when a plugin updates its tables.
Plugins, Manual Cleanup and WP-CLI Compared
There are three ways to do this work, and each suits a different person. Cleanup plugins are friendly and quick, manual SQL is precise, and WP-CLI is repeatable. The table below compares them.
| Method | Best For | Strengths | Risks |
|---|---|---|---|
| Cleanup plugin | Site owners without shell access | Fast, scheduled cleanups, no SQL needed | Broad “clean all” options, another plugin to maintain |
| Manual SQL in phpMyAdmin | Targeted fixes such as autoload | Full control, see exactly what changes | Typos and copied curly quotes can break or delete data |
| WP-CLI | Developers and agencies with many sites | Scriptable, works across sites, no browser timeouts | Needs shell access and care with commands |
Whichever you choose, use only one cleanup tool at a time. Two plugins scheduling overlapping cleanups is a common source of confusion, and if you copy SQL from any guide, retype the quotes as straight quotes before you run it.
A Maintenance Schedule That Keeps It Clean
A one-time cleanup feels good, but the database starts growing again the next day. The lasting fix is a light, regular routine that catches problems while they are small:
- Monthly: delete expired transients, empty spam and trash, check Site Health for the autoload warning.
- Quarterly: review the largest tables and the top autoloaded options, prune plugin logs, check Action Scheduler for failed jobs.
- After removing a plugin: look for its leftover options and tables, and remove them if you will not reinstall it.
- Twice a year: run table optimization during quiet hours after a fresh backup.
This schedule keeps cleanup boring, which is exactly the point. Combined with a revision limit and a working cron, most sites never need another big rescue, and the backups that protect the site stay small enough to restore quickly.
How Webueno Handles WordPress Database Optimization
At Webueno, database cleanup is part of our speed optimization service, alongside minification, removal of unused code, WebP conversion, lazy loading, plugin and theme work, Cloudflare CDN and PHP tuning. We measure first, review autoloaded options and large tables by hand and test changes before they reach the live site. On Webueno hosting we can also add Redis object caching, which keeps options and transients in memory instead of the database.
Our hosting runs on DigitalOcean with one site per server, so no neighbor’s database load affects yours, and staging and live run on the same server, which makes testing a cleanup realistic. Per-site PHP versions and included migration round it out.
The ongoing side is covered by maintenance plans at $10 per site for Monthly, $20 for Weekly and $60 for Daily, run through the Webueno Care Assistant plugin with a monthly automatic ticket, visual inspection and form checks. Uptime monitoring checks every 3 minutes and alerts after 3 failed pings, about 9 minutes, and the Daily Backups add-on keeps daily offsite copies of files, database and configuration, which is exactly what you want before any DELETE.
One-off work is billed in credits, where 1 credit is 1 hour of work at $42, and bundles save up to 21%. Agencies get a white-label agency account and set their own margin, so database cleanup for client sites becomes a service under their own brand.
Keep the Database Lean and the Site Fast
Good database optimization is less about deleting and more about knowing what to delete. Measure the tables, fix heavy autoloaded options carefully, clear revisions, transients and spam on a schedule, keep WooCommerce queues healthy and optimize tables only after a backup. Done that way, the site gets faster and nothing breaks along the way.
If you would rather have the measuring, cleanup and testing handled for you, create your Webueno account and open a ticket for a database review on any site you manage.
Frequently Asked Questions
These are the questions we hear most often from site owners and agencies planning a database cleanup.
How Often Should I Optimize My WordPress Database?
Light cleanup, such as expired transients, spam and trash, works well monthly. Review large tables and autoloaded options every few months, and run full table optimization a couple of times a year or after a big cleanup.
Is It Safe to Delete All Transients?
Yes, in principle, because transients are temporary by design and plugins rebuild them. Deleting only expired ones is gentler, since removing all of them forces every cache to rebuild at once and can briefly slow a busy site.
What Is a Good Size for Autoloaded Options?
WordPress Site Health warns when autoloaded data passes 800 KB. Well-maintained sites usually stay far below that. If you see the warning, review the largest autoloaded options and switch off or remove the ones that do not need to load on every page.
Will a Database Cleanup Plugin Speed Up My Site?
It can, especially by removing revisions, transients and spam. The biggest gains often come from autoloaded options and slow plugin queries, though, which most one-click cleanups do not review carefully.
Do I Need to Back Up Before Optimizing the Database?
Always. Deleted rows cannot be recovered without a backup. Take a full backup of files and database, confirm it restores and test larger changes on a staging copy first.
Does OPTIMIZE TABLE Work on InnoDB Tables?
Yes. InnoDB maps it to a table rebuild plus a statistics update, so you may see a “recreate + analyze” message. It reclaims space after large deletes but needs free disk space and can briefly lock large tables.