[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, Meta APIs  |     Version:  3.6
 Severity:  normal              |  Resolution:
 Keywords:  has-patch           |     Focuses:  performance
--------------------------------+------------------------------

Comment (by jeichorn):

 At Pagely on a semi-regular basis we end up with sites with hundreds of
 thousands of rows in wp_options.  The cause of this is normally storing
 sessions in wp_options.  In a case like this having the index on autoload
 is huge win.

 Before index
 mysql> explain select option_name, option_value from wp_options where
 autoload = 'yes';
 +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
 | id | select_type | table      | type | possible_keys | key  | key_len |
 ref  | rows   | Extra       |
 +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
 |  1 | SIMPLE      | wp_options | ALL  | NULL          | NULL | NULL    |
 NULL | 807663 | Using where |
 +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+

 After index:
 mysql> explain select option_name, option_value from wp_options where
 autoload = 'yes';
 +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
 | id | select_type | table      | type | possible_keys | key      |
 key_len | ref   | rows | Extra                 |
 +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+
 |  1 | SIMPLE      | wp_options | ref  | autoload      | autoload | 62
 | const |  265 | Using index condition |
 +----+-------------+------------+------+---------------+----------+---------+-------+------+-----------------------+

 We are talking about reducing query time by 1 second in this instance.  As
 the average size of wp_options grows and grows its a nice low cost index
 to add that protects people from plugins that use wp_options as a temp
 data store.

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