[wp-hackers] Meta tables: Take 5

[Mike Schinkel]

Ok, I've noodled on this a bit now and would like to suggest something to essentially resolve any performance concerns:

If we create a meta table whose type field is an ENUM then we can end up with relatively small index for locating any given object's meta records unless you get into the tens of million records.  ENUM fields take 2 bytes per record and are trivial when added to the 20 byte bigints. Those of you who know how indexes are constructed know that a 22 byte key is much much better than much larger keys like 255+ bytes. 

Note my example presumes moving all meta into one table, include meta for post and page.

CREATE TABLE wp_meta (
  meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  type ENUM('unknown','post','page',comment','link') DEFAULT 'unknown' NOT NULL, 
  object_id bigint(20) unsigned NOT NULL DEFAULT '0',
  meta_key varchar(255) DEFAULT NULL,
  meta_value longtext,
  PRIMARY KEY meta_id (meta_id),
  KEY (type,object_id),
  KEY meta_key (meta_key)
) 

There are two downsides to this (that I know of):

1.) Using ENUMs makes synchronization between two databases more fragile because ENUMs could be listed in different orders. Having routines that encapsulate this handling would resolve most of this. 

2.) A core function to register the new type would need to be able to ALTER the table and I don't know if this will be an issue on "highly secure" environments, i.e. 

ALTER TABLE wp_meta CHANGE type type ENUM('unknown','post','page',comment','link','mycustom') DEFAULT 'unknown' NOT NULL  ;

Thoughts?

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