Slow Query for the wp_options table

Question

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).

Answer 1

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.

Answer 2

I stumbled across the query mentioned in mytop running on my server a few days ago - and it actually took quite some time (about 10 seconds) for each query! So there are real-world situations where wp_options might grow to problematic size. In my case I suspect the caching plugin Cachify to be responsible for bloating wp_options.

Data of this particular wp_options:

5,309 rows
130MB of data

As a solution, I added the index similar to the solution posted by Vinay Pai, which solved the problem flawlessly.

Answer 3

My wp_options table only had about 235 rows of data. I tried indexing the table, but it didn't help.

Turns out that about 150 transient options had been inserted into the table, but hadn't been automatically deleted.

I don't know if it is related or not, but I'd been looking through my /var/log/apache2/access.log files and noticed that multiple (presumably compromised) Amazon Web Services servers (IP addresses beginning with 54.X.X.X and 32.X.X.X) had been attempting to exploit /~web-root-dir/xmlrpc.php.

After some troubleshooting, I queried the wp_options table for option names that contained "transient"

select * from wp_options where option_name like '%transient%';

One of the fields returned from this query is 'option_value' which has a datatype of LONGTEXT. According to the mySQL docs, a LONGTEXT field (for each row) can hold up to 4-Gigabytes of data.

When I executed the query, some of the rows (remember were working with those containing "transient") had massive amounts of data in the option_value field. Looking through the results, I also saw what looked like attempts to inject commands into the wp-cron process with the hopes they would be executed during the cron cycle(s).

My solution was to delete all of the "transient" rows. This will not hurt the server since "transient" rows will automatically repopulate (if they are supposed to be there).

After doing this, the server was once again responsive.

Query to delete these rows:

DELETE from wp_options where option_name like '%transient%';

I've added the AWS IP address /8 superblocks to my firewall, too (-:

Answer 4

Update related to Index for autoload field in wp_options table:

An index has been added to wp_options.autoload in WordPress 5.3. See WordPress 5.3 Changelog

Even you can see that Index is available for other columns in wp_options table also:

SHOW INDEX from wp_options