[wp-hackers] geocomments?

[John Joseph Bachir]

I thought I'd throw in my $0.02 on the structure of the options table.

Considering:

-the most common selective query on the options table selects on 
option_name

-in mysql, a primary key dictates the order in which the data should be 
stored in the "physical" file on disk. another way of saying this is that 
the "main" data file for a table can effectively act as its own best 
index. selecting on the primary key provides b-tree speed access to all of 
the columns in the table, without having to first consult a separate 
index.

So, the best way to optimize retrieving arbitrary columns when selecting 
on an option_name is to make the option_name the primary key. Of course, 
this contradicts with the general guideline of making a primary key column 
as short as possible. But since this selection is by far the most 
frequent, and joins on this table are rare or non-existant, I still think 
this would be the best option.

However I do not have extensive experience working with tables that use 
non-numeric coulmns as their primary key so perhaps I am missing something 
here.

current indexes:
   PRIMARY KEY (option_id,blog_id,option_name)
   KEY option_name (option_name)

what i propose:
   PRIMARY KEY (option_name,blog_id,option_id)
   KEY option_name (option_id)

Of course, this demands that option_name be unique... which I just noticed 
isn't currently the case. Does WP need to allow for multiple options with 
the same option_name?

John
----
aim/yim/msn/jabber.org: johnjosephbachir
713.494.2704
irc://irc.freenode.net/lyceum
http://lyceum.ibiblio.org/
http://blog.johnjosephbachir.org/