How come `wp_options` table does not have an index on `autoload`?

Question

In the beginning of each page served by WordPress, there is a MySQL call to fetch options:

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Because there's no index on autoload column, MySQL has to lookup ALL rows.

I also came across the comment of this answer saying there would be no performance gain even if there was an index.

In my application, I used a lot of transient values to serve as a session replacement. They worked great and I have my own garbage collection routines. I noticed that in the wp_options table, my transient values (the ones beginning with _transient_) all have autoload=no. I expect the number of rows of my wp_options table to increase as the number of concurrent user increases.

I'd like to know why the table is designed this way. And should I create an index for my particular case?

Answer 1

There is no index because the need for it was never strong enough.

In ticket #14258 it was suggested, but since most options use autoload=yes by default, the index would be ignored anyway.

There is also the still open ticket #24044 _Add index to wp_options to aid/improve performance_.

I think you should create an index. It will survive upgrades. It might not help your performance, but you could add real statistical data to that ticket.

---

Update November 2019

The index has been added to WordPress 5.3. Finally. See the ticket #24044 mentioned above and the developer notes for the release.

Note that if you have an existing index with the same name, you will get a warning during the upgrade.

From the changeset:

Most sites will be unaffected by this change, but those with a large number of rows in wp_options, only a small number of which have autoload set, will see a significant performance improvement.
Sites with a large number of rows in wp_options, with many of them having autoload set will unfortunately see a performance penalty on top of the already very slow queries they're running, but this should be the minority of cases.

Answer 2

I'm running 3 WP blogs on a Debian Squeeze large instance and was investigating why mysql was stuck on that host at 200% CPU usage and system load between 3 and 6. Looking at a 'show process list' inside mysql, we understood the wp_option table was involved in this issue so we executed:

alter table wp_options add index autoload_idx(`autoload`);

After this operation mysql load as shown in top drastically fell down to 1% and the instance load average is now 0.10.

We are using some plugins so there could be a loop somewhere in the code, and this might be a particular situation, but in our case the change in performances is utterly astonishing.

Our wp_options table has 347 rows.

Answer 3

While @fuxia accepted answer touches on some "claimed" reasons (most of which were claimed by employees of Automattic on various Trac tickets, etc), the underlying reason for WordPress Core not including an index for the autoload options within the wp_options table is is that Automattic worried it would negatively impact the performance of MySQL databases that were still using the MyISAM engine.

Specifically, they pointed to the WordPress.org website itself, being a very old/complex database, as an example website whose performance would be hurt by such an index.

Nearly all the other reasons for not adding the index for the past 9 years (yes, since 2010 in the case of Trac ticket #14258 and since 2013 in the case of Trac ticket #24044) were repeatedly proven incorrect by dozens of members of the WordPress community, yet the Automattic employees involved repeatedly ignored several independent benchmark tests and reverted back to mentioning MyISAM concerns.

Thankfully, in late 2019 with PHP 7.2 now the "default" version recommended by WordPress Core, and with the InnoDB engine now default in versions of MySQL after 5.5, and with continued pressure from various developers including @DanBUK who stayed on the issue for years, Automattic finally gave in and decided to add the autoload index as of WordPress 5.3+ in November 2019.

We at LittleBizzy had launched the first known plugin that automatically added the index if it did not exist, which is still available on GitHub and being downloaded regularly. Please note that you NO LONGER need to be installing such plugins to your WordPress stack if you are running WP Core 5.3+...