[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:  (none)
     Type:  enhancement         |      Status:  reopened
 Priority:  normal              |   Milestone:  Awaiting Review
Component:  Options, Meta APIs  |     Version:
 Severity:  normal              |  Resolution:
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+------------------------------
Changes (by frodeborli):

 * severity:  major => normal


Comment:

 Adding this index will improve concurrency for all sites, even though the
 perceived performance gain is minimal.

 Doing 50000 row table scans may take only 0.03 seconds - but the machine
 is using 100% CPU for those 0.03 seconds. CPU that could be used by PHP,
 the web server, the firewall or any other service running on the server.

 Also, in ordinary LRU caching schemes, reading the entire table from disk
 to do a table scan will cause valuable file system memory caching space to
 be wasted.

 The cost of adding the index is minimal/non-existent and the benefits are
 obvious for those users that do benefit from it.

 I measured this on a customer:

 Before index - query appeared in slow query log:

     # Query_time: 0.038143  Lock_time: 0.000081 Rows_sent: 1156
 Rows_examined: 50089
     SELECT option_name, option_value FROM wp_options WHERE autoload =
 'yes';

 After index, turned on profiling because it was no longer in slow queries
 log:

     show profiles;
 +----------+------------+---------------------------------------------------------------------------------+
     | Query_ID | Duration   | Query
 |
 +----------+------------+---------------------------------------------------------------------------------+
     |        2 | 0.00617425 | SELECT option_name, option_value FROM
 wp_options WHERE autoload = 'yes'         |
 +----------+------------+---------------------------------------------------------------------------------+

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