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

  • 1
    I’m voting to close this question because this was asked 8 years ago. In recent updates of WP this issue has been resolved. Commented Nov 18, 2020 at 12:15

4 Answers 4


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.

  • 2
    I doubt an index in this case would offer any gain - check out this article on cardinality. Commented Nov 6, 2012 at 14:50
  • Depends on whether most options are set to autoload or not. I would think not, but in the table shouldn't ever get so big anyway so something fishy is going on.
    – Vinay Pai
    Commented Nov 6, 2012 at 15:05
  • 1
    I updated by answer to add a bit about checking the distribution of values.
    – Vinay Pai
    Commented Nov 6, 2012 at 15:14
  • 1
    I just noticed the comment, and realized my answer is completely wrong. The query isn't actually slow... it's just being logged in the slow query log because it doesn't use an index.
    – Vinay Pai
    Commented Nov 6, 2012 at 17:00
  • 2
    Thanks to this question and answer I discovered I had 90k entries in my wp_options table, 88.5k of which were set to autoload false. The rest were all "transient" entries added by plugins (presumably for caching?). Adding an index to the autoload column dropped my mySql load from an average of 89% to 2.5% instantly. Monitoring agents show my site's response time has dropped from 1900ms to 500ms. This was a gamechanger for me.
    – Mordred
    Commented Mar 25, 2017 at 3:41

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.


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 (-:

  • Yep. I was suffering from "40 seconds load time" too until I discovered I had 20,000 wp_option records with massive data in them being loaded with every single page. Removing those considerably sped up the site.
    – JasonGenX
    Commented Feb 12, 2019 at 17:34

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.