[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:  new
 Priority:  normal                       |   Milestone:  Awaiting Review
Component:  Options and Meta             |     Version:  3.6
 Severity:  normal                       |  Resolution:
 Keywords:  has-patch reporter-feedback  |     Focuses:  performance
-----------------------------------------+------------------------------

Comment (by pento):

 Okay, here are some test results! All of this was on MySQL 5.5, I didn't
 test on other versions. The MyISAM results should be largely the same on
 other versions, the InnoDB results may be a little slower on older
 versions, but I don't expect there to be massive differences.

 Anyway, here are the variables tested:
 * Engine: InnoDB or MyISAM
 * Rows: 100, 1000, 10000, 100000, 1000000
 * Percentage of rows selected: 5%, 20%, 50%, 80%, 95%

 The full testing script and results are attached, but here's the summary:

 * Adding this index causes nearly all queries to be slower, except for
 InnoDB at 100k rows. I have no idea why this is the case.
 * Once MyISAM gets to 100K rows, adding the index makes it exponentially
 slower.
 * I killed the test script before it finished MyISAM 1M rows, because each
 query was taking > 2 minutes to run.

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