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

 The test results showed that while the speed improvement is common for
 `innodb` table, but is the exact opposite for `myisam` tables. Therefore,
 it's not a change with a low risk as it has the potential to make many
 sites slower.

 In the last few years, with [https://wordpress.org/about/stats/ mysql 5.5+
 becoming much more popular], it's likely that new sites are using
 `innodb`, but I don't know if we have any stats on `innodb` vs `myisam`.
 @pento - Any idea on that?

 An issue with only implementing the change on sites that are using
 `innodb`  for the options table is that people move DBs around without
 knowing the versions.  This was seen a lot during the switch from `utf8`
 to `utf8mb4`

 For the sites that benefit from this index, I do encourage them to add the
 index themselves (with a prefix).  Core DB migrations don't remove
 individually added indexes.

 If someone wants to work up some tests on BIT, with an index, without an
 index, on multiple versions of MySQL using both innodb and myisam than we
 can explore that as an option. A change like that would need to be
 committed early in a dev cycle (like we are right now) but can't be made
 without data showing it would be beneficial for some (though ideally, it
 would be beneficial for everyone) and have no little to no harm for
 everyone.

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