[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 miqrogroove):

 As long as we're piling back on this ticket, someone explain why a column
 that is never used (option_id) has become the primary key?  ryan, does the
 performance of an unused key ever count for anything in DB performance?
 Let's also dispel some of the misinformation.  MySQL never returns column
 values from indexes on BLOBs.

 >If a query uses only columns from a table that are numeric and that form
 a leftmost prefix for some key, the selected values may be retrieved from
 the index tree for greater speed

 Indexes on BLOBs do nothing unless the BLOB is in a WHERE condition.
 Unique keys never increase performance.  If someone in the world has an
 options table with 60,000 rows in it and only 100 are autoload true, then
 MySQL can optimize the query based on row count.

 >MySQL uses index cardinality estimates only in join optimization.

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