5

I have a "Listing" post type with 30+ meta keys which I feel like can cause a strain on the database when there's 100+ posts. Usually what I do is save each meta value 1 by 1 like so:

if(isset($_POST['_item_stock']) && !empty($_POST['_item_stock']))
    update_post_meta($post_id, '_item_stock', sanitize_text_field($_POST['_item_stock']));
else
    delete_post_meta($post_id, '_item_stock');

I do this because if I need more control over validation, I have it. It's organized and nice looking in the code. The Drawback being that it's lengthy and it saves each meta individually in the database.

My other option is to standardize all my meta into an array then serialize it. The Drawback to this method is that I can't run any meta queries on serialized data, so filtering searches or post ordering is out the window.

The final option is to do half and half, serialize what I don't need for queries and everything else I save individually. While it may be save some overhead I don't want it to look messy in my code or have any kind of disconnect.

My question is - How do you normally handle posts that have many meta keys? Is the database overhead as bad as I imagine when I save each meta key individually? Which method do you think is the best and why?

4
  • Do you have a list of these attributes?
    – Tom J Nowell
    May 9, 2014 at 15:55
  • I do and if you think it's relevant I can post them, but this is more of a general question on the best way to handle bulk keys.
    – Howdy_McGee
    May 9, 2014 at 16:03
  • There are potential answers involving custom taxonomies but without examples it's difficult to write a 'definitive' answer
    – Tom J Nowell
    May 10, 2014 at 14:27
  • 2
    Worry about 30+ meta key values when you have 100,000+ posts, not when you only have 100+ posts. At only 3000+ meta records, MySQL is blisteringly fast. IOW, don't worry about it. If you do get to 100,000+ records, move to a better server and start leveraging intelligent caching. May 13, 2014 at 22:00

1 Answer 1

5

You are facing an XY Problem. The only reason to save meta values each as a single row, would be if you need them to be searchable. If you need specific sanitization rules, use either filter_var_array() or sanitize the values, then build the array and finally save the value.

Gist with a comprehensive filter_var_array() example here.

6
  • So you're saying it's more efficient to go with option 3, save out the single values used for search and serialize the rest. You don't think that's messy when reviewing your code though?
    – Howdy_McGee
    May 9, 2014 at 16:06
  • @Howdy_McGee What does "messy when reviewing [your/my] code" mean?
    – kaiser
    May 9, 2014 at 17:32
  • 1
    I guess when I view the code, it just doesn't seem as organized and meaningful than when adding them one by one. But that's most likely just me. The answers I was kind of looking for here was either going to be "There's not too much database overhead when adding them individually" or "Serialize data you don't need in to use in search / queries" which is pretty much what I got. Thanks!
    – Howdy_McGee
    May 9, 2014 at 17:46
  • 1
    @Howdy_McGee You can attach callbacks instead of simple values + flags for filter_var_array(). This way you can easily organize your code and avoid god functions/methods.
    – kaiser
    May 9, 2014 at 18:55
  • 1
    filter_var_array - nice!
    – djb
    May 14, 2014 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.