[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            |  
------------------------------+---------------------------------------------
Changes (by barry):

  * status:  closed => reopened
  * resolution:  fixed =>


Comment:

 Can someone explain why

 UNIQUE autoloaded_options (autoload, option_name)

 makes sense?

 1) If we already have a UNIQUE index on option_name, having another one on
 (autoload, option_name) is redundant and just adds overhead on
 updates/inserts etc.  So....  if we were going to add an index here, it
 seems that something like

 KEY autoload (autoload)

 is what we would want.

 2) Assuming the index proposed in #1 is what we have, we run into another
 problem - the index is never used.  The autoload field in every WP install
 I have seen just has 2 values (yes/no) making the cardinality of that
 index 2 which means it's effectiveness as an index is almost none.

 Some EXPLAIN output from a case where this index is useful I think is
 necessary to keep it in core.  In my tests, MySQL didn't even use the
 index b/c the optimizer thought a full table scan would be faster due to
 the low cardinality of the index in question.  I think by adding
 option_name to the index and increasing the cardinality you can trick the
 MySQL optimizer to use the index in some cases, but I still don't see how
 it would be more efficient.

 I propose dropping this autoloaded_options index unless it can be shown
 (with EXPLAIN output) to be useful.

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