WordPress.org

Make WordPress Core

Opened 7 years ago

Closed 6 years ago

#3364 closed enhancement (wontfix)

Metadata db improvements

Reported by: dougal Owned by: dougal
Milestone: Priority: low
Severity: normal Version: 2.1
Component: Optimization Keywords: meta metadata postmeta usermeta catmeta postcustom custom needs-patch
Focuses: Cc:

Description

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.

Change History (7)

comment:1 westi7 years ago

  • Milestone changed from 2.1 to 2.2

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?

comment:2 markjaquith7 years ago

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.

comment:3 foolswisdom7 years ago

  • Milestone changed from 2.2 to 2.3

comment:4 Nazgul7 years ago

  • Milestone changed from 2.3 (trunk) to 2.4 (future)

comment:5 darkdragon6 years ago

  • Keywords needs-patch added

comment:6 darkdragon6 years ago

  • Milestone changed from 2.4 to 2.5

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

comment:7 Nazgul6 years ago

  • Milestone 2.6 deleted
  • Resolution set to wontfix
  • Status changed from new to closed

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

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

Note: See TracTickets for help on using tickets.