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.