The Options Table and the Autoload Tax

A page that took 200 ms takes 320 ms, and nothing in the slow query log is over 50 ms. The database CPU graph is flat. Somebody suggests more PHP memory, which changes nothing, and then a caching plugin, which hides the problem on the pages it caches.

The cause is usually one row in wp_options. It is a plugin cache, or an export, or a settings array with a hundred defaults in it, and it is marked for autoload. On its own it costs one or two milliseconds to read. The reason it matters is that it is read on every request that does not already have the options blob in memory, and on a site without a persistent object cache that is every request forever.

This lesson is about finding those rows, deciding what each one is, and changing the flag safely. The audit is five minutes of SQL. The part that takes judgement is the classification, because three of the four common cases are safe to demote and one is not.

wp_load_alloptions() reads the whole set or nothing

During wp-settings.php, long before WordPress knows whether the request is a page view or a REST call, it calls wp_load_alloptions(). That function checks the object cache for one key, alloptions in the options group, and on a miss runs a single query for every row whose autoload value is in the autoload set. The whole result becomes one cache entry: an array keyed by option name.

// wp-includes/option.php, condensed. WordPress 7.0.7.
$alloptions = wp_cache_get( 'alloptions', 'options' );

if ( ! $alloptions ) {
    $alloptions_db = $wpdb->get_results(
        "SELECT option_name, option_value FROM $wpdb->options"
        . " WHERE autoload IN ( '" . implode( "', '", esc_sql( wp_autoload_values_to_autoload() ) ) . "' )"
    );
    // ... build the array, then:
    wp_cache_add( 'alloptions', $alloptions, 'options' );
}

Two things follow from the shape of that function. The cache entry is one blob rather than one entry per option, so a large row is read and unserialised by every request, including requests that never call get_option() for it. And the write path reaches back into it: after update_option() commits the UPDATE, it calls wp_load_alloptions( true ) to force a fresh read of the entire set, patch the one key, and cache the array again. A plugin that updates a counter on every page view and autoloads that counter has arranged for the blob to be rebuilt on every request that writes.

That size threshold is wp_max_autoloaded_option_size, 150000 bytes by default, applied on the wp_default_autoload_value filter. It only governs values WordPress chooses for you. An explicit true still wins, which is how a 900 KB row ends up autoloaded in the first place. I confirmed both paths on the lab install: add_option( 'x', $blob ) with a 900 KB value and no autoload argument stored auto-off, while add_option( 'x', $blob, '', true ) stored on.

The audit is one query, and the predicate depends on your version

You do not need a profiler or a plugin dashboard for the first pass. You need the largest autoloaded rows and the total size of the set.

-- WordPress 6.6 and newer
SELECT option_name,
       LENGTH(option_value) AS bytes,
       autoload
FROM wp_options
WHERE autoload IN ( 'on', 'auto-on', 'auto' )
ORDER BY bytes DESC
LIMIT 20;

-- WordPress 6.5 and older
SELECT option_name,
       LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY bytes DESC
LIMIT 20;

Note that the 6.6 predicate omits 'yes'. Plenty of installs still carry rows written by older code, so if you are auditing a site that was upgraded rather than installed fresh, search for both:

SELECT autoload,
       COUNT(*) AS rows_autoloaded,
       SUM(LENGTH(option_value)) AS bytes
FROM wp_options
GROUP BY autoload
ORDER BY bytes DESC;

On the lab install for this lesson, with two 900 KB rows in place, that returns 1.88 MB across 122 rows in the autoload set. The same site with those two rows demoted returns 41 KB across 120 rows. The pair of numbers is the report worth keeping: the total tells you how much every uncached request reads, and the count tells you whether you have one whale or four hundred minnows.

Size decides the order you work in, not the verdict

The total matters more than any single row, and it decides how you spend the next hour. Forty kilobytes means the set is healthy and any performance work belongs somewhere else entirely. Two megabytes means a plugin is treating the options table as storage, and the fix is in that plugin rather than in your configuration.

Total autoloaded	What it usually means	Where to look
Under 100 KB	Normal. Rewrite rules, the cron array, a handful of settings.	Nowhere.
100–500 KB	One plugin storing its whole settings object, including defaults no request reads.	Rows over 50 KB.
500 KB – 2 MB	A cache written through the options API: menu structures, term lookups, an export.	Names ending in _cache, _data, _index.
Over 2 MB	Something is storing a table in a single row. It will fail on write before it fails on read.	Everything, urgently.

The healthy end of that table has a texture worth knowing. After the two bad rows were demoted, the largest autoloaded row on the lab install was a core transient holding block CSS, at 22,750 bytes, followed by wp_user_roles at 3,133 and cron at 1,046. Nothing there is a mistake. That is what a normal options blob looks like, and it is why an audit that finds nothing is a real result.

Four classes of accidental autoload, and only one is subtle

Sorting the offenders by name is enough to classify almost all of them.

Plugin caches written through the options API. The signature is a pair of rows, one holding a payload and one holding a timestamp, often named myplugin_product_cache. Demoting is safe: the plugin reads the row by name, so it does not care whether the value arrived in the blob. This is the largest win in most audits.

Orphaned transients. A transient with no expiry is stored as an ordinary option. WordPress writes those with autoload off, so they do not inflate the blob, but they grow the table indefinitely and they appear in any size-ordered list. If nothing reads a non-expiring transient, delete it rather than demote it. Demoting changes nothing about a row that was never autoloaded.

Stale rewrite rules. The rewrite_rules row is legitimately large and legitimately autoloaded; on a normal site it is a few kilobytes and it belongs in the set. The problem case is the row that is rewritten constantly, which is what an unconditional flush_rewrite_rules() does. That call belongs in an activation hook and nowhere else. Demoting rewrite_rules will not fix a site that flushes on every request; it will add a second problem on top of the first.

Oversized settings arrays. This is the subtle one. A plugin with forty fields and a hundred defaults stored as one serialised array is not doing anything wrong, and the front end may genuinely read two values out of it. The array is still the wrong shape for autoload, because the blob pays for all hundred and forty values on every request so that two can be read. Demote it, and verify the read pattern before you decide, because this is the class where demoting can make a page slower (see the next section).

Demoting is safe when the plugin reads the row by name

The change is one call, and there is a trap in it. update_option() returns early when the value you pass matches the value already stored, before it touches the autoload column at all. Passing the value you have read straight back to it therefore does nothing to the flag:

// This looks like it works. On WordPress 7.0.7 it changes the flag to 'on'
// and leaves it there, because the value is unchanged.
$value = get_option( 'myplugin_product_cache', array() );
update_option( 'myplugin_product_cache', $value, false );

That is the trap, and the fix is to change only the flag. WordPress has helpers for exactly this, added in 6.4. They take booleans, write the modern column values, and reach the column whether or not the value changed:

// One option, or many at once. Available since WordPress 6.4.
wp_set_option_autoload( 'myplugin_product_cache', false );
wp_set_options_autoload( array( 'myplugin_a_cache', 'myplugin_b_cache' ), false );

I verified both paths on the lab install with a 900 KB row. The value-and-flag call left autoload at on; the helper left it at off. If you are on an older version and have to use update_option(), write a value that actually differs, which is usually a one-off maintenance script rather than something you keep.

Verify with the database rather than with the settings screen, and check that the value survived a trip through the plugin's own admin page:

wp db query "SELECT option_name, autoload, LENGTH(option_value) AS bytes
  FROM wp_options WHERE option_name = 'myplugin_product_cache';"

# The alloptions cache entry is now stale. Flush it before you measure.
wp cache flush

# And confirm the plugin's front-end page still renders the value.
curl -s -o /dev/null -w '%{http_code} %{time_total}\n' https://example.test/

If the flag comes back after a settings save, you have three options and they are not equivalent. Leave it and accept that the flag returns occasionally, which is fine for a row written once a quarter and wrong for one written hourly. Patch the plugin where it writes, replacing a bare update_option( $key, $value ) with an explicit false. Or intercept the write from a must-use plugin of your own.

// wp-content/mu-plugins/autoload-guard.php
add_action( 'updated_option', function ( $option ) {
    global $wpdb;

    if ( 'myplugin_product_cache' !== $option ) {
        return;
    }

    $wpdb->update(
        $wpdb->options,
        array( 'autoload' => 'off' ),
        array( 'option_name' => $option )
    );

    // The blob every request reads is now stale.
    wp_cache_delete( 'alloptions', 'options' );
}, 10, 1 );

That last one reaches past the options API and should be treated as the stopgap it is. The honest sequence is: measure, remove the flag, confirm the plugin still works, then report it upstream so the fix lives where it belongs. A must-use guard is for a plugin you cannot wait on.

Turning autoload off trades one wide read for many narrow ones

This is the trade that decides whether demoting a given row helps, so measure it before you commit. I ran the autoloaded query 200 times on the lab install with both large rows in place, then again with both demoted, and took the median and p95 of the whole read-and- unserialise cycle.

Autoloaded set	Rows	Bytes	Median	p95
Two 900 KB rows in the set	122	1,884,532	1.34 ms	1.85 ms
Both rows demoted	120	41,334	0.55 ms	1.20 ms

About 0.8 ms per request on this machine, on a request that had a warm PHP process and a warm MySQL buffer pool. The number is small because the machine is fast and the row is only 900 KB. The shape of the number is what to remember: the cost of an autoloaded row is paid by every request, including the requests that never look at it.

Now the other half. Demote a row that the request path reads ten times, and you have converted one wide read into ten narrow ones, because each get_option() on a non-autoloaded row is a separate lookup. On the lab install the front page issues four SELECT option_value FROM wp_options WHERE option_name = '?' statements, one per non-autoloaded option the template asks for. Four is fine. Thirty would not be.

So the rule is not "demote everything large". It is this: an option belongs in the autoload set when the request path reads it on essentially every request and it is small. Everything else does not, however convenient the current arrangement is. When you demote something, load the page that reads it and count the statements again.

Show the solution and the reasoning

The classification drives the action, and the four classes have four different answers.

A plugin cache gets demoted with wp_set_option_autoload( $name, false ), then verified with a database query, then a cache flush, then a load of the front-end page that uses the plugin. If the flag returns after a settings save, that is expected and it is the moment to decide between accepting it, patching the write, or adding the must-use guard.

An orphaned transient gets deleted, not demoted. The test is whether anything reads it: grep the codebase and the plugin directory for the transient name without the _transient_ prefix, then delete it from the options table if nothing does. Remember that a non-expiring transient never appears in the expired-row cleanup, so nothing else is going to remove it for you.

Stale rewrite rules are fixed at the source. Find the flush_rewrite_rules() call and look at the hook it is on. It belongs on activation. After you move it, flush once by hand and leave the row autoloaded, because that is what it is for.

An oversized settings array is the one where the read count decides. If the front end reads one or two keys from it, demote the whole array and accept a small number of extra option lookups, which is usually a good trade. If the front end reads eight keys from it, the better fix is for the plugin to store the frequently read values as their own small autoloaded options and keep the big array non-autoloaded, or to move the bulk data into a custom table. Both of those are plugin changes, so write it up rather than patching it in place.

One result to expect and not misread: after demoting, the page's statement count may go up by one per read of that row while the total database time goes down. That is the trade working, and it is why the lab asks for both numbers rather than the byte total alone. If the statement count goes up by more than the number of distinct reads, you are looking at an N+1 loop from the previous lesson, not at an autoload decision.