[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:  Performance             |     Version:  3.6
 Severity:  normal                  |  Resolution:
 Keywords:  has-patch dev-feedback  |
------------------------------------+------------------------------
Changes (by cliffseal):

 * keywords:  has-patch => has-patch dev-feedback


Comment:

 On a WordPress 'app' in which lots of non-autoloaded transients are used,
 I got similar results, running the same queries as @krazybean.

 Before:

 {{{
 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 | 8196 | Using where |
 +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
 1 row in set (0.01 sec)
 }}}

 After:

 {{{
 mysql> create index i1 on wp_options(autoload, option_name,
 option_value(50));
 Query OK, 0 rows affected (0.52 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 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  | i1            | i1   | 62      |
 const |  155 | Using where |
 +----+-------------+------------+------+---------------+------+---------+-------+------+-------------+
 1 row in set (0.00 sec)
 }}}

 Understood this may not affect the general population, but I wonder how we
 could best help folks who make extensive use of transients in certain
 situations.

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