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

 Replying to [comment:40 barry]:
 > 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

 In a query where both autoload and option_name are in the WHERE clause,
 mysql will be able to use this index for both columns. In a query where
 only option_name is in the WHERE clause, mysql will not use this index (or
 at least that was the case a few years ago, maybe it has changed.  I think
 Oracle does try to use such indexes in such cases, which of course
 requires "ignoring" the first level of the b-tree)

 > KEY autoload (autoload)
 >
 > is what we would want.

 In a query that uses both autoload and option_name in the where clause,
 MySQL will only use the option_name index, because it typically only uses
 one index per table per query.


 > 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.

 That's very true about the low cardinality (although I've never understood
 why that's the case, and several things I've read don't explain it. If the
 data is evenly split 50/50 with a cardinality of 2, shouldn't an index on
 that column cut down the tablescan time by 50%?)

 At any rate-- as I noted in a comment above, I've never seen a slow query
 log entry based on a select on autoload, so for whatever reason MySQL
 doesn't end up needing it. Although, jdub above notes an instance where
 adding an index on it solved a problem for a particular installation.
 jdub, do you have any more info for us?

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