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?

As far as I can tell from reading through #24044, old MyISAM tables would get a performance regression, new InnoDB tables would mostly benefit. I am converting all my legacy tables to InnoDB, and setting an index on autoload column.
– lkraavJul 19 '18 at 16:50

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.

Thanks for sharing. I figured WordPress did have a defect in handling this option table. It's so small, that it shouldn't be querying. It should be select * once for all. Instead, it's querying for each options, that's why putting an index will make a big difference.
– He ShimingDec 17 '13 at 8:42