Wordpress slow queries and transient data in options table
Slow queries are a major pain in the ass and should be avoided by all means necessary if the site in question is under heavy load. It's bad for performance. Very bad.
The problem from the title is by definition a Wordpress design problem. By default Wordpress has few entries in the options table and the risk of running into slow queries here is virtually non existing unless you have a serious hardware issue. Wordpress however offers the transients API. Basically a caching facility utilizing the options table. A transient is basically an option with an expiration date. A cache entry.
The fundamental problem is this.
The options table is a core table and queried every time a page is rendered. One fundamental query is this one.
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
The query basically loads everything that's supposed to be autoloaded. Broadly speaking config stuff. That is the primary purpose of the options table and unless it's used in another way there's no problem at all. On a standard installation the table shouldn't be that big and about 2/3rd are actually autoloaded. If however plugins utilize the transients API heavily this can cause a major bottleneck. The table is not designed to house thousands, hundreds of thousands or even millions of entries. I had a couple of crazy plugin setups which placed over 2 million entries into the table. To no surprise slow queries was a very kind description for the performance issue. The problem however doesn't start with hundreds of thousands of entries. It starts - depending on your hardware - with thousands or tens of thousands. So even on a normal site this can cause performance issues if problematic plugins are used.
As a note to developers. Do not use the transients API without careful consideration. It is not capable and not meant to cache large volumes of entries and it's a major design flaw to utilize the options table for this rather than a dedicated caching table. It's hands down the most retarded design decision I've seen in Wordpress and it has quite a few candidates. If you need to cache - i.e. Twitter feeds - do it in a custom table. The options table only works without problems if you have few entries. If that table grows out of scope you'll create a bottleneck. Your caching entries can easily be offloaded to memcache. This however does not solve the problem for the actual options.
Now how do you fix the issue if you can't replace or don't want to replace plugins that are heavily polluting the options table?
There's a rather easy fix. You need to add an index. Currently like so...
CREATE INDEX autoload ON wp_options(autoload, option_name);
This index basically allows Wordpress to separate the crap from the important stuff. Not everything but mostly everything. Caching entries won't be autoloaded and thus the usual candidate for slow queries above will perform significantly better.
But remember. This is only a solution if the table is heavily used for caching. If something else is going on this solution might not work as expected. In virtually all cases however the problem will be some plugin.
The general stance by Wordpress on this issue is to use something like memcache. While this does reduce the problem with caching entries it does not affect the original options problem since they are not cache entries.
I can't say not indexing the table by default on autoload is a bad call. It's not necessary under normal conditions. The bad call really is using that table for caching in the first place. There should be a dedicated caching table which then can be offloaded to a kv store like memcache.
If you encounter slow queries on the query above check your options table. Chances are that some plugins are offloading truckloads of data into that table. If this is the case the index solution works wonders on the performance of that query. If however there's not much else than autoloaded stuff it won't solve the problem.
As a general note. As I already mentioned I have no clue whatsoever why one would use an options table to store caching entries. Making assumptions on volumes is dangerous and it's totally obvious that heavy usage of the transients API will cause problems if the table grows. And it grows depending on expiration date and general load. So you can make no qualified assumption that this table will not house hundreds of thousands or even millions of entries. While these entries can be offloaded into a kv store in memory they are still retained in the table. So offloading does solve any problem with retrieving cache entries. It however does not solve any issues with options. Using a dedicated caching table for the transients would solve this problem on a much more generic level.