WordPress database optimization starts with the wp_options table, not with an “optimize tables” button. On every request, WordPress loads all autoloaded options into memory in one query. When plugins leave large or forgotten options set to autoload, every page pays for them, cached or not. Since WordPress 6.6, Site Health warns when autoloaded options get too large. After autoload, look at expired transients, orphaned metadata, revisions, plugin log tables and missing indexes. Measure first with SQL or WP-CLI, back up, then clean up in small steps.
Most slow WordPress databases I see aren’t slow because of size. They’re slow because of a few specific things: an autoload blob that grew for years, a plugin storing logs in wp_options, a postmeta query without a usable index. A database with a million rows can be fast. A small one with 4 MB of autoloaded options isn’t. This page is part of my guide on how to speed up WordPress.
What is wp_options autoloaded data?
Each row in wp_options has an autoload column. Rows marked to autoload are fetched together early in every request and kept in the alloptions cache, so later calls to get_option() don’t hit the database. That’s a good design for small settings that are needed everywhere, like the site URL or active plugins.
It breaks down when a plugin autoloads something big that only one admin screen uses: a serialized cache, a list of every product, debug output. With a persistent object cache, the whole alloptions array is stored as one cache entry, so a large one also costs memory and transfer on every request. Removing a plugin often leaves its options behind, still set to autoload.
WordPress 6.6 changed how this works. The autoload column now accepts on, off, auto, auto-on and auto-off, alongside the legacy yes and no. When a plugin doesn’t say whether an option should autoload, WordPress decides, and large options are no longer autoloaded by default. Site Health also gained a check that flags the total size of autoloaded options when it goes over a limit, which is filterable.
How do you measure autoloaded options?
Start with Site Health under Tools. If it reports autoloaded options as a problem, you already know where to look. For actual numbers, use SQL. Replace wp_ with your table prefix (wp db prefix prints it). The IN list covers both the 6.6 values and the legacy one:
-- Total size of autoloaded options
SELECT COUNT(*) AS options, ROUND(SUM(LENGTH(option_value)) / 1024) AS kb
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto');
-- The 20 largest autoloaded options
SELECT option_name, ROUND(LENGTH(option_value) / 1024, 1) AS kb, autoload
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 20;You can run those with wp db query "..." or in any MySQL client. The option names usually tell you which plugin they belong to: a prefix like wpseo_, elementor_ or woocommerce_. Anything with a prefix from a plugin you no longer have is a cleanup candidate.
How do you fix large autoloaded options?
For each large option, decide one of three things:
- It belongs to a removed plugin. Delete it, after a backup.
- It belongs to an active plugin but isn’t needed on every page. Set it to not autoload, and test the screens that plugin uses.
- It’s needed everywhere and is large because the plugin keeps growing it. Report it to the plugin author, or replace the plugin. Turning off autoload just moves the query.
WP-CLI handles each case without writing SQL by hand:
# Always first
wp db export before-cleanup.sql
# Check and change autoload for one option
wp option get-autoload some_plugin_cache
wp option set-autoload some_plugin_cache off
# Delete an option left by a removed plugin
wp option delete old_plugin_settings
# Clear the object cache after direct changes
wp cache flushIn code, plugin developers have wp_set_option_autoload() since WordPress 6.4, and the $autoload argument of add_option() and update_option(). If you build plugins, pass false for anything that isn’t needed on most requests.
What else slows a WordPress database down?
Once autoload is under control, these are the next things I check, ordered by how often they turn out to matter:
| Problem | How to check | What to do |
|---|---|---|
Expired transients piling up in wp_options | wp transient list --format=count | wp transient delete --expired; with a persistent object cache, transients move out of the table |
| Slow plugin queries | Query Monitor, MySQL slow query log | Fix or replace the plugin, add an index where the query pattern justifies it |
| Plugin log and stats tables | wp db size --tables | Set a retention period in the plugin, or prune old rows |
| WooCommerce Action Scheduler history | Size of the actionscheduler tables, failed and pending counts | Fix the failing actions first, then let retention clean completed ones |
| Orphaned post meta | The query below | Delete after a backup |
| Unlimited revisions | wp post list --post_type=revision --format=count | Set WP_POST_REVISIONS; delete old revisions if the count is huge |
| Tables on MyISAM | SHOW TABLE STATUS | Convert to InnoDB, during a maintenance window |
Orphaned post meta is metadata whose post no longer exists. This counts it:
SELECT COUNT(*)
FROM wp_postmeta pm
LEFT JOIN wp_posts p ON p.ID = pm.post_id
WHERE p.ID IS NULL;For slow queries, the MySQL slow query log is the honest source. Query Monitor shows you the queries of the page you’re looking at. The slow log shows you what hurts across all traffic, including cron and REST calls. Turn it on with slow_query_log and set long_query_time to a threshold that fits your server, then read it after a normal day.
On large sites, especially WooCommerce, the default WordPress indexes on wp_postmeta and wp_usermeta don’t fit every query pattern. The Index WP MySQL For Speed plugin adds indexes designed for common WordPress queries. Run EXPLAIN on your slow queries before and after so you know whether it changed anything for your site.
Do plugins help with WordPress database optimization?
They’re fine for the routine part: expired transients, revisions, spam comments, trash. What they can’t do is decide whether a 900 KB option belongs to a plugin you still use. That needs a person reading option names. I’d also be careful with any “optimize tables” feature on large InnoDB tables. OPTIMIZE TABLE rebuilds the table, which can take a long time and lock writes on a busy site. wp db optimize does the same thing from the command line. Run it during low traffic, if at all. Reclaiming disk space rarely changes response times.
How does the database fit with caching?
A page cache hides database problems from anonymous visitors, which is why many sites look fast on PageSpeed and feel slow in wp-admin. A Redis object cache for WordPress cuts repeat queries for everyone else, but it can’t fix a query that’s slow the first time, and it stores the alloptions blob as is. Clean the database, then cache it. If you want the full picture of which cache layer handles what, read page cache vs object cache vs edge cache.
Database time shows up in server response time. After a cleanup, measure TTFB on uncached URLs and in wp-admin, as described in my TTFB guide for WordPress.
Frequently asked questions
How big should autoloaded options be?
As small as the site needs. Site Health’s warning is a useful upper bound, not a target. In practice I look at the largest individual options first, because one or two of them usually make up most of the total.
Can I set every option to autoload off?
You can, but it makes things worse. Options that every request needs would then be fetched one query at a time. Autoload is a good mechanism used badly by some plugins. Fix the outliers.
Is it safe to delete options from removed plugins?
Usually, but confirm the prefix really belongs to a plugin you removed, since some plugins share prefixes with companion add-ons. Export the database first and test the site afterwards.
If wp-admin is slow, the database keeps coming back as the suspect and you’d like the cause found and written down before anyone touches production, that’s the kind of work I do in a WordPress performance audit. When the fixes need hands on the server, the WebOption team implements them.