[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 jeichorn):

 So i just finished a set of tests using ~750 real wp_options tables on
 mysql 5.6.

 Here are the takeaways.

 For 90% of my sample set didn't matter either way.  Those sites has 2-500
 options, and autoloaded 90%+ of them.  With either Myisam or innodb index
 or not, the performance difference is in noise land.

 The queries normally took around 0.001 seconds and the speed difference
 was in the 0.0002 range.
 In general MyISAM is slightly slower in these cases, and Innodb is
 slightly faster, but I wouldn't consider either difference meaningful.

 The final 10% are mostly 2 cases.

 Sites with huge #s of options all autoloaded, this is slower in both
 myisam and innodb, but the absolute performance already sucked, and the
 php performance of autoloading 100k options is going to be a couple orders
 of magnitude worse then the db side so I think we can just ignore that
 outlier.

 The remaining case seems to be 20% or less of the options autoloaded.

 So my overall take is this change would be mostly a wash for the majority
 of site, with big performance wins for innodb in the case of small
 autoload, large # of rows (myisam is also helped in this situation).  With
 the biggest downside being for innodb as well, large rows all autoloaded.

 I'm not seeing the same huge performance losses for myisam on a large # of
 rows, though I would imagine that depending on the database configuration
 and amount of memory that could be an issue.

 The one thing none of this testing takes into account though is the worst
 case situation.  If you are scanning 100k rows in wp_options to return
 500, and do that query at volume you can easily run your database out of
 cpu, and then performance of that query will fall apart.

 Summary from my run at.
 https://gist.github.com/jeichorn/d33dfcb6ba687dd84fb65945f59a4f5a

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