0

Let's say I want to build a function that allows my users to like posts in my website.

My intention is to store the ID of the user that liked the post as metadata.

My question is what's the best way to go in terms of performance and 'queribility'.

Separate row for each like:

'liked_by'=>'3'
'liked_by'=>'21'
'liked_by'=>'17'

Or just one row to hold a serialized array (json) with all IDs

'liked_by'=>array('3','21','17')

Considere I would like to store the date of each like too.

array( $user_id => $current_date )

Else take in consideration that I most ensure that all post metadata is useful for queries!

EDIT...

I used the "likes" example for simplicity, but in reality the function I want to create for my application is for users to pay to unlock extra content per post. The market for my applications isn't too big, lets say I don't expect more than 400 unlocks per post, but that means 400 additional rows per post just to hold the users that unlocked the post content + the date of each unlock.

7
  • the first method is usefull if you need to search e.g. where liked_by = 21. – mmm Apr 12 '18 at 13:11
  • I will need that. I most ensure I can use every little piece of metadata to query my posts. – Luis Rivera Apr 12 '18 at 13:12
  • Any sort of array will make querying very difficult. Use separate rows. – Jacob Peattie Apr 12 '18 at 13:15
  • @JacobPeattie I thought about this, but I'm just worried of my postmeta table end up nasty and heavy. Just imagine a post with 1000 likes means 1000 rows in the wp_postmeta table just for 1 post. – Luis Rivera Apr 12 '18 at 13:19
  • I guess it's a matter of perspective. To me that feels clean and fast compared to bundling it up. Simple fact is that even if you find it messy, the alternative of using a serialised array should be discarded immediately, as it's a very poor way to store information that needs to be queried. The best option would probably be to have a totally custom table, but that will involve more work to save to it and retrieve values, and to extend WP_Query to be able to use it for queries. – Jacob Peattie Apr 12 '18 at 13:28
1

Neither option are the correct option for your use case

Your question is barking up the wrong tree, because you neglected to mention that you intend to search for these posts based on the values the meta holds.

If you're displaying a post, you can use either method, though the first is cleaner/simpler to handle in code. get_post_meta is fast and relies on table indexes, and WP fetches the post meta in advance anyway.

The problem here is if you have thousands of post meta, or a handful of hyper big post meta, in the extreme cases this can cause you to run out of memory or prevent it being cached by object caches

But What If You Searched For Them?

Showing a list of users who liked the post is going to be fast, but showing a list of posts a user liked?

This is what would happen to your sites speed and performance:

enter image description here

Searching for posts by their post meta is very, very bad. I've seen sites running on huge servers crippled by just a handful of these. It's a huge slowdown, the kind of query that could take 10-20 seconds if it finishes at all once you have more than a handful of posts.

In this case, you would face a new problem too, if you had chosen option 2, the very ability to find those posts would become an issue. Thus option 1 would be superior, but performance would still be terrible

A Marginally Superior Solution With the Opposite Tradeoff

Use user meta to store the post IDs, rather than post meta to store the user IDs. Still has issues, aka showing a list of users who liked or unlocked a post is now super expensive. But showing a user all the posts they've liked is fast.

The Real Solution, and a General Rule of Thumb

If you need to store something, and you need to search/find/filter or show posts that have this special information, use a taxonomy.

In this case, a taxonomy named unlocked_by, where the term slug/name is the user ID that liked it.

Now your query is super fast:

$q = new WP_Query([
    'unlocked_by' => get_current_user_id()
]);

In fact, it's so fast, you don't even need that query, WP created a post archive at /unlocked_by/2/ ( I'm user number 2 btw ), assuming you enabled that.

You could even add the parameter via pre_get_posts so it happens transparently.

The wp_get_object_terms and wp_get_object_terms functions are your friend here

6
  • "The Real Solution" may be the solution indeed. Probably I was overlooking, to be honest, I did not even think about taxonomies. – Luis Rivera Apr 13 '18 at 17:30
  • Not sure if I should mark your answer as the correct answer. I feel this topic is very opinionated. What do you think? – Luis Rivera Apr 13 '18 at 17:32
  • The problem is that the question you wanted answered is not the question you asked. As such this answer does answer the question, but it aims at your original problem. Eitherway, taxonomies are the solution if you need to list all posts with meta X – Tom J Nowell Apr 13 '18 at 20:09
  • You are totally right. Me trying to keep the question simple ended up getting a partial answer for my current situation. – Luis Rivera Apr 14 '18 at 12:31
  • Wondering if I should delete this question.... – Luis Rivera Apr 14 '18 at 12:32
2

Very simple, meta should not be queried as it do not scale. Once this is out of the way, you should prefer storing data as serialized array as it makes the meta table smaller and therefor all operations on it faster.

OTOH, the array method will not scale very well if your use case require updating a specific post data frequently, as the cost for each "write" operation gets higher the bigger the array is, but I don't think I ever heard of any wordpress site that found this to be a bottle neck.

5
  • I used the "likes" example for simplicity, but in reality the function I want to create for my application is for users to pay to unlock extra content per post. The market for my applications isn't too big, lets say I don't expect more than 400 unlocks per post, but that means 400 rows per post. – Luis Rivera Apr 12 '18 at 13:26
  • 2
    This doesn't sound like something you should store in meta. Financial transactions usually deserve their own tracking by using a CPT, or some other table, as you usually want to query for a specific transaction or a set of them. – Mark Kaplun Apr 12 '18 at 13:37
  • note that arrays will not scale past a certain point, object caches etc have minimum sizes, and there's overhead, so for a large site this will cause other problems. Also keep in mind that DB's are built for millions of rows, on the expectation that you won't be doing table scans – Tom J Nowell Apr 12 '18 at 15:07
  • @TomJNowell, not sure what you mean in "object caches etc have minimum sizes, and there's overhead,". Obviously there is an overhead, but isn't the data going to be stored in exactly the same way under both options? Doesn't the object cache stores just a serialized meta data? – Mark Kaplun Apr 12 '18 at 15:31
  • A lot of object caches have a maximum size, configurable mind – Tom J Nowell Apr 12 '18 at 16:27

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.