[wp-hackers] Meta tables: Take 5

[Mike Schinkel]

On Aug 4, 2009, at 5:20 PM, Peter Westwood wrote:
> On 27 Jul 2009, at 17:47, Mike Schinkel wrote:
>>> Why so large? I was thinking CHAR(20) at most.
>> Take a look at wp_postmeta and wp_usermeta; meta_key is  
>> VARCHAR(255).  I presumed the same for this.
>> Even so, ENUM is still a lot more efficient and it provides nice  
>> "documentation in code" for what types are valid.
>
> Please don't use ENUM as it is a mysqlism
> We switched away from it [1] to make our DB schema more portable and  
> to make it easier for people to write alternative database classes.
> [1] http://core.trac.wordpress.org/ticket/4778

Note that Otto said it was a very bad idea, but was overruled by  
Matt.  Unfortunately VARCHAR is a poor choice for an index key on any  
SQL database.

I must ask, is database portability for WordPress ever a reasonable  
and realistic goal?  I think we may be kidding ourselves if we think  
so, and worse I think adding database portability would bloat  
WordPress core significantly and make it much harder to make  
conforming plugins.

Ideally I'd like to see us give up on the (false?) hope of database  
portability and return to ENUMs. Failing that, a portable alternate  
would be to move from VARCHAR(20) to SMALLINT(2) with a table that  
defines the keys. That would make a big difference in scalability as  
the number of rows increase and additional metadata is added.

I've added a ticket into track for discussion: http://core.trac.wordpress.org/ticket/10546

-Mike Schinkel
WordPress Custom Plugins
http://mikeschinkel.com/custom-wordpress-plugins/