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']));
    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?

  • Do you have a list of these attributes? – Tom J Nowell May 9 '14 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 '14 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 '14 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. – MikeSchinkel May 13 '14 at 22:00

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.

| improve this answer | |
  • 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 '14 at 16:06
  • @Howdy_McGee What does "messy when reviewing [your/my] code" mean? – kaiser May 9 '14 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 '14 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 '14 at 18:55
  • 1
    filter_var_array - nice! – djb May 14 '14 at 19:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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