Metadata db improvements

[dougal]

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.

[westi]

This sounds like a good idea todo.

Should it not be targetted for 2.2 rather than 2.1 though to allow more time for testing etc?

[markjaquith]

This would only break postmeta, I think. Usermeta userid + key combos are already unique, so that should be fine.

There would need to be an API implemented (and preferably a full version ahead of these changes) for things like querying by value, mySQL LIKE queries, etc.

[darkdragon]

Since this is non-trivial and does not have a patch, I'm pretty sure it should be moved to the next version.

[Nazgul]

No traction in over a year, so closing as wontfix.

Feel free to re-open if you have additional information/patches/suggestions/...