[wp-trac] [WordPress Trac] #2699: Make option_name index unique

[WordPress Trac]

#2699: Make option_name index unique
------------------------------+---------------------------------------------
 Reporter:  johnjosephbachir  |        Owner:  ryan    
     Type:  task (blessed)    |       Status:  reopened
 Priority:  high              |    Milestone:  2.9     
Component:  Optimization      |      Version:  2.8     
 Severity:  normal            |   Resolution:          
 Keywords:  tested            |  
------------------------------+---------------------------------------------

Comment(by barry):

 Replying to [comment:42 Denis-de-Bernardy]:

 > mysql> create index option_autoload on www_options ( autoload );
 > Query OK, 246 rows affected (0.42 sec)
 > Records: 246  Duplicates: 0  Warnings: 0
 >
 > mysql> explain select * from www_options where autoload = 'yes';
 >
 +----+-------------+-------------+------+-----------------+-----------------+---------+-------+------+-------------+
 > | id | select_type | table       | type | possible_keys   | key
 | key_len | ref   | rows | Extra       |
 >
 +----+-------------+-------------+------+-----------------+-----------------+---------+-------+------+-------------+
 > |  1 | SIMPLE      | www_options | ref  | option_autoload |
 option_autoload | 62      | const |  216 | Using where |
 >
 +----+-------------+-------------+------+-----------------+-----------------+---------+-------+------+-------------+
 > 1 row in set (0.00 sec)
 > }}}

 What is the count of yes vs no options in your table?  In most WP installs
 I have seen, the vast majority are autoload=yes and that is the only case
 we query for in bulk meaning we are returning the majority of the rows in
 the table thus the index is not used.

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