[wp-trac] [WordPress Trac] #10546: Change VARCHAR keys to SMALLINT

[WordPress Trac]

#10546: Change VARCHAR keys to SMALLINT
--------------------------+-------------------------------------------------
 Reporter:  mikeschinkel  |       Owner:                                             
     Type:  enhancement   |      Status:  new                                        
 Priority:  normal        |   Milestone:  Unassigned                                 
Component:  General       |     Version:                                             
 Severity:  normal        |    Keywords:  enum keys portability mysql meta relational
--------------------------+-------------------------------------------------
 Back in 2.4 all ENUMs where changed to VARCHAR(20) for reasons of
 portability(FN1).  That move addressed an abstract concern while
 introducing two (2) tangible concerns: 1.) bloating of indexes and 2.)
 reduction of scalability.  This is especially a concern as the discussion
 for adding meta data for more types (i.e. links, comments, etc.) has the
 potential to add more meta records.

 Personally I'd really like to see a move back to ENUMs (which are an
 excellent feature of MySQL) but failing that I'd recommend we add a
 wp_meta table with fields ID (smallint) and Name (varchar(255)) and use
 smallint foreign keys linking to wp_meta whereever varchar(20) keys are
 currently used.  One or the other solution addresses these concerns, but
 status quo does not.

 Footnotes:

 FN1 - While the portability concern probably seemed like a good idea at
 the time it appears the concern may be little more than academic (FN2,FN3)
 especially considering how few plugins or themes would work with other
 databases.

 FN2 - http://codex.wordpress.org/Using_Alternative_Databases

 FN3 - http://wordpress.org/support/topic/549

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/10546>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software