[wp-trac] [WordPress Trac] #3364: Metadata db improvements

[WordPress Trac]

#3364: Metadata db improvements
--------------------------+-------------------------------------------------
 Reporter:  dougal        |       Owner:  dougal                                                         
     Type:  enhancement   |      Status:  new                                                            
 Priority:  low           |   Milestone:  2.1                                                            
Component:  Optimization  |     Version:  2.1                                                            
 Severity:  normal        |    Keywords:  meta, metadata, postmeta, usermeta, catmeta, postcustom, custom
--------------------------+-------------------------------------------------
 Some disussion on the bbPress bbDev mailing list came up about our
 implementation of postmeta (and of course the other metas, by extension).
 We currently have a schema that includes 4 fields: {{{meta_id}}},
 {{{post_id}}}, {{{meta_key}}}, and {{{meta_value}}}. The {{{meta_id}}} key
 is really only needed because the {{{post_id}}}+{{{meta_key}}} combination
 is not unique.

 I originally designed it this way on purpose, to maximize the flexibility
 and simplicity of the system. However, if we made
 {{{post_id}}}+{{{meta_key}}} unique, it could improve the database
 performance for meta queries. Since the bbPress API is still more liquid,
 they are planning to go ahead and make this modification. Making a similar
 change in WP would not only improve the db optimization when fetching
 metadata, but would maintain consistency between the two sister
 applications.

 I think we can do this while maintaining the current API functions,
 because we already 'smush' duplicate keys in the database into a single
 cache key with an array of values. Basically, we want to make the database
 structure mimic the PHP data structure that we're already using. This
 should be pretty easy to do by just adding a little bit of automatic
 serialization/deserialization, and using REPLACE instead of INSERT in
 {{{add_post_meta()}}} and friends.

 The main concern here is whether it might break existing plugins. But I
 think we can make the change transparent to code that uses the API
 functions (rather than direct db queries). And even code that is using
 direct queries shouldn't require much change, just some magic
 serialization/deserialization on the values. I'm going to try to find time
 to work on patches for this, and maybe work up some test cases. In the
 meantime, I'd appreciate any feedback from anyone who has an opinion on
 this.

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