Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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

share|improve this answer
2  
I doubt an index in this case would offer any gain - check out this article on cardinality. –  TheDeadMedic Nov 6 '12 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 Nov 6 '12 at 15:05
1  
I updated by answer to add a bit about checking the distribution of values. –  Vinay Pai Nov 6 '12 at 15:14
    
The index won't specifically reduce the execution time, but it will prevent the full table scan and associated blocks. –  Stephen Nov 6 '12 at 15:45
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 Nov 6 '12 at 17:00
show 1 more comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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