Managing the wp_options Table and Autoload

This is the in-depth companion to the wp_options performance warning in the FAQ. It shows you how to find which plugins are bloating your options table, switch options off in a way you can cleanly reverse, and measure the effect, so you cut memory and CPU on every page load without accidentally slowing pages down.

How autoload works, and why it matters

On every request, WordPress loads every option whose autoload is set to yes (and, since WordPress 6.6, also on or auto) into a single in-memory array, the “alloptions” cache. A handful of small options is fine. Hundreds or thousands of rows, or a few very large ones, means every page builds a big array and burns RAM and PHP time before it does anything else. The goal is to stop the large, rarely-needed options autoloading, while leaving the small ones that most pages actually use.

Before you start: back up

These are UPDATE and DELETE statements against a core table, so take a database backup first, and ideally test on a staging copy. Run the SQL with a desktop client over SSH such as Beekeeper Studio or TablePlus rather than exposing your database through wp-admin. If you are on WordPress older than 6.6, replace every autoload IN ('yes','on','auto') below with autoload = 'yes'.

Step 1: Clear stale transients

Transients belong in an object cache, not the database. Install a Redis object cache first (see the FAQ), then remove the leftover transient rows that are still sitting in wp_options:

DELETE FROM wp_options WHERE autoload IN ('yes','on','auto') AND option_name LIKE '_transient_%';
DELETE FROM wp_options WHERE autoload IN ('yes','on','auto') AND option_name LIKE '_site_transient_%';

Step 2: Find the culprits

Options do not have a plugin column, but each plugin prefixes its option names, so grouping by that prefix tells you who is responsible. Look at it two ways: which prefixes have the most autoloaded options (count), and which take the most space (size).

Group by plugin, by count

SELECT SUBSTRING_INDEX(option_name, '_', 2) AS prefix,
       COUNT(*) AS autoloaded_options
FROM wp_options
WHERE autoload IN ('yes','on','auto')
GROUP BY prefix
ORDER BY autoloaded_options DESC
LIMIT 30;

Group by plugin, by size

SELECT SUBSTRING_INDEX(option_name, '_', 2) AS prefix,
       COUNT(*) AS autoloaded_options,
       ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS total_mb
FROM wp_options
WHERE autoload IN ('yes','on','auto')
GROUP BY prefix
ORDER BY total_mb DESC
LIMIT 30;

The size view usually matters more: one giant autoloaded option (a cache blob, a log, a huge settings array) can hurt more than hundreds of tiny ones. To see the single biggest offenders, and the total you are loading on every page:

SELECT option_name, ROUND(LENGTH(option_value)/1024, 1) AS kb
FROM wp_options
WHERE autoload IN ('yes','on','auto')
ORDER BY LENGTH(option_value) DESC
LIMIT 30;

SELECT COUNT(*) AS rows_autoloaded,
       ROUND(SUM(LENGTH(option_value))/1024/1024, 2) AS total_mb
FROM wp_options
WHERE autoload IN ('yes','on','auto');

SUBSTRING_INDEX(option_name, '_', 2) groups by the first two underscore-separated parts of the name; change the 2 to 1 or 3 to widen or narrow the grouping.

Step 3: Turn options off reversibly with a no-load-01 marker

The usual advice is to set autoload = 'no'. The problem is that plenty of options are legitimately no already, so once you have set yours to no too, you cannot tell which ones you changed, and you cannot cleanly undo. Instead, set them to a distinct marker value such as no-load-01. WordPress only autoloads options whose autoload is yes, on or auto, so any other value (including no-load-01) stops them loading, but because it is your own unique marker you can find and revert exactly the rows you touched. Use no-load-01, no-load-02 and so on for successive batches, so you can revert one batch at a time.

Switch specific options off:

UPDATE wp_options
SET autoload = 'no-load-01'
WHERE option_name IN ('some_big_option', 'another_big_option');

Or switch off a whole prefix you have decided is not needed on every page:

UPDATE wp_options
SET autoload = 'no-load-01'
WHERE autoload IN ('yes','on','auto')
  AND option_name LIKE 'bloatyplugin_%';

Revert a single option, or an entire batch, whenever you need to:

UPDATE wp_options SET autoload = 'yes' WHERE option_name = 'some_big_option';

UPDATE wp_options SET autoload = 'yes' WHERE autoload = 'no-load-01';

Prefer to delete options that clearly belong to plugins you have removed long ago (old Mailchimp, RevSlider and the like); use the no-load-01 marker for options from plugins you still use but that do not need to load on every page.

To delete a removed plugin’s leftover options, match them by the prefix you saw in the grouping queries above. Always run the SELECT first and eyeball the list, then run the matching DELETE.

-- Worked example: you removed Revolution Slider long ago, but its options remain.
-- 1. Preview exactly what will go (always check the list before deleting):
SELECT option_name, autoload, ROUND(LENGTH(option_value)/1024, 1) AS kb
FROM wp_options
WHERE option_name LIKE 'revslider%'
ORDER BY LENGTH(option_value) DESC;

-- 2. Happy with the list? Delete them:
DELETE FROM wp_options WHERE option_name LIKE 'revslider%';

Swap revslider for the prefix of whatever you are removing, for example mc4wp_% for an old Mailchimp for WordPress install, or wpseo_% only if you have genuinely removed Yoast. Keep the prefix specific (include the trailing underscore where the plugin uses one) so you do not catch unrelated options, which is exactly what the preview SELECT is for. Only delete options for plugins you have truly uninstalled; for plugins you still use, switch options off with the no-load-01 marker instead.

Step 4: Measure the result with Query Monitor

Do not guess, measure. Install the free Query Monitor plugin, load a representative front-end page while logged in as an admin, and read two numbers before and after your changes:

• Peak memory usage (Query Monitor’s Overview panel, also shown in the admin bar). Moving large options out of autoload should lower this. That is your RAM reduction.

• Total database queries and total query time (the Queries panel). A smaller alloptions load often reduces page-generation time.

Take the numbers first, make your change, then reload and compare. Clear any page cache and object cache between runs so you are comparing like with like.

Watch out for more queries: when to swap back

There is a trade-off. An autoloaded option is fetched once, inside the single alloptions load. An option that is not autoloaded is fetched with its own SELECT the first time something calls get_option() for it on a page (unless a persistent object cache already holds it). So if you switch off an option that a page genuinely needs, you have swapped one shared load for a per-page query, and your query count goes up.

After your changes, if Query Monitor shows the query count has risen, open the Database Queries panel and look for options-table SELECTs for the names you moved (Query Monitor shows the calling code). Any option that now runs a query on most page loads should go back to autoloading:

UPDATE wp_options SET autoload = 'yes' WHERE option_name = 'the_option_you_moved';

Rule of thumb: move options that are large and rarely read (settings for admin-only screens, one-off caches, dead plugins). Leave small options that are read on nearly every request autoloaded, because the single bulk load is cheaper than a separate query each time.

When you are happy, re-check the Scalability Pro warning: the autoloaded count and size should be down. For the quick version of this, see the FAQ entry. Questions? Ask us on Discord.

Further reading

Related products

Scalability ProSpeed up your whole site – big catalogues, big databases, no re-architecture