I have been tracking the slow queries log of the WP based site (with the default value of the a long_query_time set to 10), and I have noticed that the following query is often getting logged –
# User@Host: root[root] @ localhost []
# Query_time: 0 Lock_time: 0 Rows_sent: 394 Rows_examined: 458
SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';
I do not understand how such a small table can take so much time to execute. Is this just a symptom of some other problem? (Currently running Moodle, phpbb and WP on a dedicated VM).
Update: The reason the query is being logged is it doesn’t use an index. The query time is 0, i.e. it actually executes fast. You can unset the “log-queries-not-using-indexes” option if you don’t want these to be logged.
The wp_options table has no index on autoload (it now should, it was added to WP core schema Aug 15, 2019), so the query ends up doing a full table scan. In general that table shouldn’t get too large, so it’s not a problem, but I’m guessing that’s somehow happened in your case.
Adding an index might solve the problem, but as TheDeadMedic pointed out in the comments, it might not if the values of autoload are either majority yes, or evenly distributed between yes and no:
First, do this query to see what the distribution looks like:
SELECT COUNT(*), autoload FROM wp_options GROUP BY autoload;
if a large majority of them are set to ‘no’, you can solve the problem for now by adding an index on autoload.
ALTER TABLE wp_options ADD INDEX (`autoload`);
However, you might want to get to the bottom of why that table has gotten too large. Possibly some badly written plugin doing something fishy.
