[wp-trac] [WordPress Trac] #24044: Add index to wp_options to aid/improve performance

[WordPress Trac]

#24044: Add index to wp_options to aid/improve performance
--------------------------------+------------------------------
 Reporter:  DanBUK              |       Owner:
     Type:  enhancement         |      Status:  reopened
 Priority:  normal              |   Milestone:  Awaiting Review
Component:  Options, Meta APIs  |     Version:
 Severity:  major               |  Resolution:
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+------------------------------

Comment (by jkhongusc):

 +1 to changing autoload field to another type like TINYINT or
 ENUM('no','yes') if you dont add an index to the autoload field

 University of Southern California's (USC) WP site was hit by this issue
 luckily during move-in week.  We use WP as an Enterprise portal. All USC
 members (up to 100k users) access our WP portal as a gateway for services.
 Every users's portal page is unique = no page caching.

 USC has:
 mysql InnoDB
 lots of users which indirectly has lots of wp_options rows
 uses transients to store complex queries/data
 does not use an object cache (work in progress)
 huge database server sized for our usage + growth (8 vCPUs, 16GB RAM)

 One day our database CPU went to 100%.  I noticed lots of slow queries
 (doing full table scans) -
 SELECT option_name, option_value FROM mywp_options WHERE autoload = 'yes';
 I immediately dropped an index on it and the cpu usage went from 100% to
 under 5%:
 create index uscautoload on wp_options(autoload, option_name);

 Crisis averted and I get to keep my job.  I was the one who pushed to use
 WP as our portal

--
Ticket URL: <https://core.trac.wordpress.org/ticket/24044#comment:54>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform