[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 Denis-de-Bernardy):

 Replying to [comment:40 barry]:
 > KEY autoload (autoload)
 >
 > is what we would want.

 Barry is actually right, here. It's really meant for fetching all
 autoloaded options. The unique index on option name is fine for queries
 that have both in the where clause. I'll attach a patch in a sec.


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

 Actually... and best I'm aware of course, index use mostly depends on the
 size and data distribution in the table. Too little rows, or too common
 values, mean the index isn't used. The odds are strong this rules our your
 test site.

 My live site, for instance, has had plenty of plugins over time. The
 options table is cluttered with leftover junk from old plugins, and old
 features such as cached RSS and so on. And now transients, etc. Here's
 what my april 2009 dump returns:

 {{{
 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)
 }}}

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