Optimizing wp_options for Speed

The options table in the WordPress database often becomes a performance bottleneck, especially on older, and perhaps more complex sites. In this article we’ll look at some insights, tools and tips to help you take control over your wp_options table.

The Options API

The WordPress Options API design is simple, and yet its internal caching and lookup mechanism isn’t that straightforward, even for the more experienced developers. We won’t go into the details of how object caching works with options, but here are a few things that you should always keep in mind:

  • Option lookups are cached
  • Most options will be served from cache
  • All options set to autoload are retrieved and cached in memory, long before a plugin or theme is loaded
  • New options are set to autoload by default
  • Most themes and plugins don’t delete their options upon deactivation/uninstall
  • Even if they do, many “uninstall” methods will never trigger that deletion

Autoloading options is a simple core mechanism which runs pretty early in wp_load_alloptions(), it basically loads all the options that have their autoload flag set to yes in the wp_options table. This mechanism allows WordPress to retrieve most of the necessary options in one go, versus having to query them one by one on demand.

To autoload, or not to autoload

When creating a new option, WordPress theme and plugin developers have the ability to set the autoload flag, which as we mentioned defaults to yes:

add_option( 'foo', 'value', '', 'yes' ); // autoload
add_option( 'bar', 'value', '', 'no' ); // don't autoload

For example, if you have a contact form on your /contacts/ page, there’s really no need to load the contact form options into memory on the rest of our website, so setting the autoload flag to no for an option like that may be a good idea.

On the other hand, if you’re displaying your contact information from an option in the footer on every single page (or most pages) on your website, having it autoload will save you a round-trip to the MySQL database on every page load.

Don’t be afraid to try both variants for your particular option, and remember, the autoload flag can easily be changed at any time in the database.

Housekeeping

Cleaning up your wp_options table from time to time is a very good idea. As we mentioned, most themes and plugins will not clean up their options upon deactivation, so after “trying out” a few plugins on your site, you can easily end up with megabytes of worthless data autoloaded into memory on every request.

Memory Usage in WordPress
Memory Usage in WordPress

You can start by getting a list of all option keys, together with the length of their values and their autoload flag:

SELECT option_name, LENGTH(option_value), autoload FROM wp_options ORDER BY autoload DESC, option_name ASC;

The goal is to delete options from themes and plugins you’re not using anymore, and most of the time you’ll recognize them from the name or prefix, at other times you’ll have to search through the codebase, and don’t forget to backup your database before deleting anything.

Keeping track of those counts

After removing all the unnecessary options, you’ll often want to set up some sort of monitoring. The simplest way would be to monitor the total number of rows in the options table, and if you see that grow past a certain threshold, you can fire off some alerts.

Monitoring WordPress Options in Munin
Monitoring WordPress Options in Munin — Rapid and continuous growth are not good signs

Munin is one of the tools we use here at Pressjitsu for monitoring all our customer nodes, so we wrote a little plugin that scans the local MySQL database for all options tables and reports their sizes. It’s multisite compatible, depends on the availability of WP-CLI, and with a little bit of Python knowledge you can further customize it to fit your needs.

Transients

The options-based Transients API is a good way to cache bits of information in WordPress. Great use cases are remote HTTP requests, RSS feeds and complex database lookups. Note that transients are not set to autoload by default, although they’ll be treated more like options if an $expiration time is not set.

So when creating a new transient, keep in mind that further retrieval of its value will cost you two database queries — one for the timeout, plus one for the actual value. Unless you’re using persistent object caching of course, in which case transients will never even hit the database.

Because of the way transients work in WordPress, manually setting the transient keys to autoload in the database may have unexpected behavior, so if you’re looking to cache something for a specific amount of time, sometimes you may be better off just using options vs transients. Here’s an example you can start with:

function get_twitter_followers() {
    $ttl = 2 * HOUR_IN_SECONDS;
    $cache = get_option( 'my_twitter_followers' );
    if ( empty( $cache['timeout'] ) || $cache['timeout'] < time() ) {
        $followers = wp_remote_get( ... );
        $cache = array(
            'count' => $followers,
            'timeout' => time() + $ttl,
        );

        update_option( 'my_twitter_followers', $cache );
    }

    return $cache['count'];
}

Outdated Transients in wp_options

Transients only expire (are deleted) when fetched, so if a theme or plugin stops fetching a transient, it'll never expire and could live in the WordPress database for a long, long time.

When it's tens or even hundreds of rows, it doesn't have any noticeable impact, but we've seen some sites with hundreds of thousands of outdated transients, enough to significantly slow down basically any query against the wp_options table, especially the one that autoloads our options.

Remember, the autoload column doesn't have an index in the wp_options table, so searching for "all options with the autoload flag" can be a lengthy operation when we have to scan hundreds of thousands of rows:

mysql> EXPLAIN SELECT * FROM wp_options WHERE autoload = 'yes';
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | wp_options | ALL  | NULL          | NULL | NULL    | NULL | 925347 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+

Of course we can try and add a new index, and sometimes it could be a good idea, but removing outdated transients from the options table will have a much bigger impact in this particular situation.

Response Times Before and After Transients Cleanup
Response Times Before and After Transients Cleanup

Here on Pressjitsu we use our Pj Transient Cleaner mu-plugin, which runs a daily scan for outdated transients and removes them in the background. You can certainly use the plugin on any other hosting environment, or you can look at alternatives from WordPress.org, some of which even have a GUI.

TLC Transients

While not too related to the wp_options topic, Mark Jaquith's TLC Transients project is a really good way of taking transients to the background, and thus significantly improving user-facing performance.

Wrapping up

Knowing what's going on with your options table is critical for WordPress performance. The difference between autoloading vs loading on demand can have a significant impact on page load times. Options pollution is a problem, and regular health checks and clean-ups ensure a better user experience for your website visitors.

Here at Pressjitsu we're obsessed with performance, we take care of all of this, plus a lot more behind the scenes for all our customers. Subscribe to our newsletter and sign up for your free 14-day trial here, no strings attached.