[wp-trac] [WordPress Trac] #62790: "autoload" index is not used

[WordPress Trac]

#62790: "autoload" index is not used
--------------------------+-----------------------------
 Reporter:  kkmuffme      |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  5.3
 Severity:  minor         |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 Added in https://core.trac.wordpress.org/ticket/24044
 This index is not used anymore by MySQL and slows down WP (bc of indexing
 cost)

 {{{
 EXPLAIN FORMAT=JSON SELECT option_name, option_value FROM wpoptions WHERE
 autoload IN ( 'yes', 'on', 'auto-on', 'auto' )
 }}}


 Why?
 - the cardinality of the index is too low, the mysql optimizer decides not
 to use the index
 - the IN() is too unspecific (e.g. if the query would be `= 'yes'` the
 index would be used)
 **=> this is actually the reason why there was a performance gain when
 this was added https://core.trac.wordpress.org/ticket/24044#comment:17 but
 why there is none now**
 - lots of things have changed in the meantime in mysql performance (e.g.
 myisam to innodb,...)

 Optionally the index can be forced like:

 {{{
 EXPLAIN FORMAT=JSON SELECT option_name, option_value FROM wpoptions FORCE
 INDEX (autoload) WHERE autoload IN ( 'yes', 'on', 'auto-on', 'auto' )
 }}}

 However I couldn't see a performance gain by forcing the index either.

 Suggestion: remove the index again, since it's not used anyway

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/62790>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform