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

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.

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.