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

 Replying to [comment:4 joemcgill]:
 > After doing a bit more testing, I am not seeing a huge performance
 benefit from removing the index either

 How did you test that? The performance "benefit" (= actually removing the
 performance penalty) of removing the index is when adding or updating data
 to the options table. Removing the index slightly speeds up
 adding/updating data and reduces CPU load. For people where this index
 didn't provide any gains, this will be minimal. For people where this
 index at some point did provide gains, the benefits of removing them will
 be bigger

 > Given that the IN clause is now generated from
 wp_autoload_values_to_autoload()

 I think this is an important point. Since using = with 1 value will still
 use the index:

 {{{
 EXPLAIN FORMAT=JSON SELECT option_name, option_value FROM wpoptions WHERE
 autoload = 'yes';
 }}}

 **What should happen, I guess, is that possibly redundant (yes, on, auto-
 on, ...) are reduced to a single value (e.g. "on") like it used to be, as
 this will make sure the index is used again.**
 While this is straightforward for yes/on no/off, someone who is familiar
 why/for what auto/auto-on was implemented should take a look at if those
 are really necessary?
 As it stands, it's faster to just update the autoload value of all options
 if the admin's/site's preference changes than using auto/auto-on/auto-off
 and thus slowing every options table query down.

 ---

 For testing it doesn't make a lot of sense to test with a default WP
 install that has maybe a few 100 or maybe 2000 rows in the table, since
 you won't ever encounter this in the wild.
 If a site is using just 3-4 plugins and not using an external object cache
 (transients!) with only 1 active user and cron running, you'll end up with
 4k+ rows in the options table.


 With a basic WooCommerce site with a few 100 products and no object cache,
 but few carts, I got around 6000 rows.
 Using:
 8.0.35-27 - Percona


 {{{
 {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "1357.52"
     },
     "table": {
       "table_name": "wpoptions",
       "access_type": "ALL",
       "possible_keys": [
         "autoload"
       ],
       "rows_examined_per_scan": 6855,
       "rows_produced_per_join": 3776,
       "filtered": "55.08",
       "cost_info": {
         "read_cost": "979.92",
         "eval_cost": "377.60",
         "prefix_cost": "1357.52",
         "data_read_per_join": "3M"
       },
       "used_columns": [
         "option_name",
         "option_value",
         "autoload"
       ],
       "attached_condition": "(`db_`.`wpoptions`.`autoload` in ('yes','on
 ','auto-on','auto'))"
     }
   }
 }
 }}}

 Forced is 4x as expensive:

 {{{
 {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "6197.83"
     },
     "table": {
       "table_name": "wpoptions",
       "access_type": "range",
       "possible_keys": [
         "autoload"
       ],
       "key": "autoload",
       "used_key_parts": [
         "autoload"
       ],
       "key_length": "82",
       "rows_examined_per_scan": 5484,
       "rows_produced_per_join": 5484,
       "filtered": "100.00",
       "index_condition": "(`db_`.`wpoptions`.`autoload` in ('yes','on
 ','auto-on','auto'))",
       "cost_info": {
         "read_cost": "5649.44",
         "eval_cost": "548.40",
         "prefix_cost": "6197.84",
         "data_read_per_join": "4M"
       },
       "used_columns": [
         "option_name",
         "option_value",
         "autoload"
       ]
     }
   }
 }

 }}}

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