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.

In the beginning of each page served by WordPress, there is a MySQL call to fetch options:

SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes';

Because there's no index on autoload column, MySQL has to lookup ALL rows.

I also came across the comment of this answer saying there would be no performance gain even if there was an index.

In my application, I used a lot of transient values to serve as a session replacement. They worked great and I have my own garbage collection routines. I noticed that in the wp_options table, my transient values (the ones beginning with _transient_) all have autoload=no. I expect the number of rows of my wp_options table to increase as the number of concurrent user increases.

I'd like to know why the table is designed this way. And should I create an index for my particular case?

share|improve this question

2 Answers 2

up vote 10 down vote accepted

There is no index because the need for it was never strong enough.

In ticket #14258 it was suggested, but since most options use autoload=yes by default, the index would be ignored anyway.

There is also the still open ticket #24044 _Add index to wp_options to aid/improve performance_.

I think you should create an index. It will survive upgrades. It might not help your performance, but you could add real statistical data to that ticket.

share|improve this answer
1  
Thank you. I'll take your suggestion. –  He Shiming May 4 '13 at 10:13

I'm running 3 WP blogs on a Debian Squeeze large instance and was investigating why mysql was stuck on that host at 200% CPU usage and system load between 3 and 6. Looking at a 'show process list' inside mysql, we understood the wp_option table was involved in this issue so we executed:

alter table wp_options add index autoload_idx(`autoload`);

After this operation mysql load as shown in top drastically fell down to 1% and the instance load average is now 0.10.

We are using some plugins so there could be a loop somewhere in the code, and this might be a particular situation, but in our case the change in performances is utterly astonishing.

Our wp_options table has 347 rows.

share|improve this answer
    
Thanks for sharing. I figured WordPress did have a defect in handling this option table. It's so small, that it shouldn't be querying. It should be select * once for all. Instead, it's querying for each options, that's why putting an index will make a big difference. –  He Shiming Dec 17 '13 at 8:42

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.