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

[WordPress Trac]

#62790: "autoload" index is not used
--------------------------+--------------------------
 Reporter:  kkmuffme      |       Owner:  joemcgill
     Type:  defect (bug)  |      Status:  reviewing
 Priority:  normal        |   Milestone:  6.8
Component:  Database      |     Version:  5.3
 Severity:  minor         |  Resolution:
 Keywords:  needs-patch   |     Focuses:  performance
--------------------------+--------------------------

Comment (by siliconforks):

 Replying to [ticket:62790 kkmuffme]:
 > Added in https://core.trac.wordpress.org/ticket/24044
 > This index is not used anymore by MySQL

 This does not appear to be true in all cases.

 I tested it on a site with several thousand rows in the `wp_options`
 table, and it appears to be using the index:

 {{{
 mysql> EXPLAIN FORMAT=JSON
     -> SELECT option_name, option_value
     -> FROM wp_options
     -> WHERE autoload IN ( 'yes', 'on', 'auto-on', 'auto' )
     -> \G
 *************************** 1. row ***************************
 EXPLAIN: {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "988.76"
     },
     "table": {
       "table_name": "wp_options",
       "access_type": "range",
       "possible_keys": [
         "autoload"
       ],
       "key": "autoload",
       "used_key_parts": [
         "autoload"
       ],
       "key_length": "82",
       "rows_examined_per_scan": 2195,
       "rows_produced_per_join": 2195,
       "filtered": "100.00",
       "index_condition": "(`**********`.`wp_options`.`autoload` in
 ('yes','on','auto-on','auto'))",
       "cost_info": {
         "read_cost": "769.26",
         "eval_cost": "219.50",
         "prefix_cost": "988.76",
         "data_read_per_join": "1M"
       },
       "used_columns": [
         "option_name",
         "option_value",
         "autoload"
       ]
     }
   }
 }

 }}}

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