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

 Hey folks -- cool to see my ticket resurrected after 3 years :-)

 Replying to [comment:25 ryan]:
 > Some WPMU installs use InnoDB for the options table.  Having a varchar
 as the primary key isn't the best for InnoDB since it prefers small,
 sequential primary keys.  Should we make option_id primary and option_name
 unique?

 That's true -- it's a pretty bad idea to make an innodb primary key non-
 numeric or non-sequential, and here we are doing both.

 Having a primary key on option_id, and then a separate unique key covering
 whatever columns we like, will provide the same performance benefits, with
 a slight increase in disk space used by that table and its indexes (but
 maybe not compared to what we had before-- I'd have to think a little
 harder to be sure). At any rate, it's a good design.

 in r11883:

 {{{
 #!sql
 PRIMARY KEY  (option_name),
 KEY option_id (option_id)
 }}}

 Best for InnoDB (and probably best or equivalent in myisam as well:)

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

 And while we're at it, having the unique key cover all of the columns in
 popular queries would allow for innodb to not have to go back to the
 primary data store to get the info, and only stay in the index, cutting
 down on a spindle access or two, per query.

 Because the most common query on that table by far is this (found in
 functions.php around line 347):

 {{{
 #!sql
 SELECT option_value FROM $wpdb->options WHERE option_name = '$setting'
 LIMIT 1
 }}}

 We could get a big performance win with this index:

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

 And that's what I recommend as the least obtrusive optimization. (assuming
 we do still want to enforce uniqueness of option_name at the DBMS layer--
 otherwise just make it a straight `INDEX`)

 Now, something to consider is that
  1. option_id is currently a `bigint`, which means it is 8 bytes. This
 restricts the number of rows in that table to (wait for it…)
 '''18,446,744,073,709,551,615'''
  1. the bigger the column on which a primary key is made, the worse the
 performance (both for space and time complexity), ''especially'' if it is
 bigger than 4 bytes (`int`).
  1. so, assuming that we are okay with that table holding only the number
 of rows allowed by `int` (4,294,967,295 -- yes, that's 4.3 billion), it
 would be beneficial to make option_id an `int`

 But-- that's the case with all the tables in the system that won't need to
 hold more than 4.2 billion entries. I can make a separate ticket for that
 if you guys think that's an idea that should be discussed further.

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