[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:  trunk
 Severity:  normal       |  Resolution:
 Keywords:  has-patch    |
-------------------------+------------------------------

Comment (by krazybean):

 On a table with above 1k rows in wp_options

 Before autoload 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 | 1460 | Using where |
 +----+-------------+------------+------+---------------+------+---------+------+------+-------------+

 After adding index named i1

 mysql> create index i1 on wp_options(autoload, option_name,
 option_value(50));

 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 |  178 | Using where |
 +----+-------------+------------+------+---------------+------+---------+-------+------+-------------+

 After the index was created the total rows searched dropped from 1460 to
 178.

--
Ticket URL: <http://core.trac.wordpress.org/ticket/24044#comment:9>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software