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?
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
autoloadset, will see a significant performance improvement.
Sites with a large number of rows in
wp_options, with many of them having
autoloadset 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.