[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:                    |  
------------------------------+---------------------------------------------

Comment(by johnjosephbachir):

 Replying to [comment:27 johnjosephbachir]:
 > the most common query on that table by far is this (found in
 functions.php around line 347):

 Actually I forgot about preloading autoloaded options (functions.php
 around line 423):

 {{{
 #!sql
 SELECT option_name, option_value FROM $wpdb->options WHERE autoload =
 'yes'
 }}}

 So, adding an index to cover that:

 {{{
 #!sql
 PRIMARY KEY  (option_id),
 UNIQUE option_name (option_name, option_value ),
 UNIQUE autoloaded_options (autoload, option_name, option_value )
 }}}

 Note that each of the indexes is needed to cover each of the mentioned
 queries, because the order of the columns is crucial.

 Actually, now I'm realizing that there isn't an index on autoload at all,
 which is kind of wild, because that means that query results in a
 tablescan on the options table. Since that table rarely gets big, MySQL's
 own on-the-fly optimizations must be sufficient (I certainly have never
 seen that query in a slow query log).

 Or maybe there aren't a lot of options with the autoload flag set.

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