Separate meta table for every post type

  • Hi guys,

    the main performance issue I keep hearing complaints about, seems to be caused by a too stuffed wp_postmeta database table (especially when using WooCommerce on top of WP)

    So I figured that there must be a way to separate the table entries by posttype and process each of them in their own separate table:

    wp_postmeta
    wp_pagemeta
    wp_productmeta
    wp_shop_ordermeta
    etc.

    So I started googling if someone else had the same idea, but no luck.

    Then I started browsing through the functions and hooks that I thought to be applicable (pre_get_posts, get_post_meta, _get_meta_table, save_posts etc.), but no luck here either; I just copied wp_postmeta into wp_pagemeta, but I can’t get the page to update in its own table.

    Am I going about this the wrong way? Or am I just silly to want this?

    I have tried several performance enhancing plugins and tools and use cronjob to periodically remove revisions and such, but besides noticing the improvement by removing records from wp_postmeta (manually!), I am just curious if there is an easy way.

    Please point me in the right direction if this is posted somewhere else or posted in the wrong forum etc.

    Cheers!

Viewing 4 replies - 1 through 4 (of 4 total)
  • Moderator bcworkz

    (@bcworkz)

    You can create custom tables, but then you’d also be responsible for managing how post meta is saved and retrieved. This can be done for your own meta data, but could be more difficult for data used by other plugins like WooCommerce. There is often a preliminary filter hook that allows us to override the default handling for a particular function. I’ve not verified if there’s one specifically for meta data. Review the associated source code to confirm. Even if such a filter exists, other plugins would need to call that function in order for your override to work. While likely, it’s not guaranteed.

    While reducing each table’s size will improve query performance, I suspect one particular table will remain heavily loaded (such as product meta), so performance improvement may not be as great as you might think. The post meta schema is inherently inefficient to start with. Repeating the same schema in more, smaller tables will help, but the same inefficiency remains.

    Ideally, the most common meta data would be better saved in a more efficiently organized table. For example, instead of relating by post ID and meta key, each meta key would be its own column in a custom table. Obviously you’d be very limited in which columns you could use, so this would have to be limited to the most commonly queried meta data. This is but one example, the best table schema depends on what data is most often queried and how it is queried.

    Thread Starter Cees Rijken

    (@connectcase)

    Hi @bcworkz, thanks for the reply.

    So basically what you’re saying is: the performance gain may be not as good as what I expect it to be, BUT… it would be a handy feature, right?

    It’s too bad there’s no readily available hook or anything, but it wouldn’t be too hard for the WP devs to just say:

    IF posttype (from wp_posts) = ‘XXX’ then lookup in wp_XXXmeta (instead of of wp_postmeta. And subsequently do this universally for every (custom or plugin) posttype….

    Moderator bcworkz

    (@bcworkz)

    It’s too bad there’s no readily available hook or anything

    I didn’t say that! ?? I was trying to say I haven’t verified for sure, but there likely could be a useful hook. The caveat being other plugins would need to use the related function. If they instead directly added data via $wpdb methods or similar, they’d bypass your filter callback. The data would end up in wp_postmeta as it does now.

    I think most plugins would use the related functions, so your callbacks would mostly be effective. For the few plugins that don’t, oh well, you can’t win them all.

    How much improvement such a scheme would manifest is dependent on the specific site. On many sites I think the gain would be negligible. But in certain situations I’m sure there would be a tangible benefit.

    I’ve a little more time today, so I went ahead and checked to see if there are useful hooks you could use. There are!
    add_post_metadata and get_post_metadata

    The above links go to the proper doc page for each hook, but I’ve not found them to help much in understanding. I recommend viewing the filter source code in context with the rest of the function declaration. Use the View on Trac/Github links below the source code on the above linked doc pages.

    Thread Starter Cees Rijken

    (@connectcase)

    Thanks for the reply! I’ll be sure to check out those hooks! Much appreciated.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Separate meta table for every post type’ is closed to new replies.