Metadata db improvements
|Reported by:||dougal||Owned by:||dougal|
|Component:||Optimization||Keywords:||meta metadata postmeta usermeta catmeta postcustom custom needs-patch|
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.