[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:
Component:  Options, Meta APIs  |     Version:
 Severity:  normal              |  Resolution:
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+--------------------------
Changes (by dandruczyk):

 * status:  closed => reopened
 * version:  3.6 =>
 * resolution:  maybelater =>


Comment:

 I was having unusual (and climbing) CPU usages and frequent showings in
 the slow query log of this query, so I added the index for each Wordpress
 blog on my SQL server (118 of them),   CPU usage for MySQL alone dropped
 by a factor of 3+  (from about 30-35% to 10%).  NO OTHER changes were
 made..

 The databases are a mix of MyISAM (older blogs) and InnoDB (newer ones).
 MySQL is 5.5.44 running on Ubuntu 14.04 64 bit on a VMware ESXi host
 backed by enterprise grade SAN storage.  The MySQL server is quad
 (virtual) core, 8GB ram.

 The change to all blogs was made at 13:20 (see picture linked below).  The
 remaining blogs had the index added over a 10 minute period starting
 around 13:30
 [http://imgur.com/qOce6KT
 ]
 This is a fairly busy MySQL server handing about 2100 queries per second
 pushing about 100Mbits/sec of traffic continuously.

 I encourage that this index be added as the drop in resource consumption
 is significant nad it has essentially no downside to having it there.

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