Building a scalable WordPress favouriting plugin – one serialised meta value array or many meta records

by

I am trying to build a simple WordPress favourite post plugin that is scalable and could handle 1000s or 10000s of users or more.

There are several different approaches that I have seen in other plugins and I would like to know which would be best practice from a scalability point of view, which means that size of the record or the number of records could be an issue.

Problem: The basic idea is that there is a button on a post that a logged in user can click to favourite the post. This must then be stored in the database so that when the user goes to that post, they cannot favourite it again and also they can view a list of their favourited posts.

Option 1: Store in both user meta and post meta tables with serialised arrays

This is what the WordPress Post Like System (https://hofmannsven.com/2013/laboratory/wordpress-post-like-system/) does. Following the click, the code retrieves _liked_posts meta key from the user meta table that stores in an array the post ids of the posts that the user has liked and it retrieves the _user_likes meta key from the post meta table that stores in an array the user ids of the users that have liked the post.

The code then appends the current post id to the _liked_posts and the current user id to _user_likes. It also increments two further meta records: post like count and a user like count.

What I like about this system is that it seems fairly simple, with only one record in user meta and one record in post meta storing who has liked what. What I don’t like is that if you have many users liking the post or a user that likes many posts those meta value arrays could get very long, which I assume could cause problems?

$meta_POSTS = get_user_meta( $user_id, "_liked_posts" ); // post ids from user meta
$meta_USERS = get_post_meta( $post_id, "_user_liked" ); // user ids from post meta
$meta_POSTS['post-'.$post_id] = $post_id; // Add post id to user meta array
$meta_USERS['user-'.$user_id] = $user_id; // add user id to post meta array
update_post_meta( $post_id, "_user_liked", $meta_USERS ); // Add user ID to post meta
update_user_meta( $user_id, "_liked_posts", $meta_POSTS ); // Add post ID to user meta

Option 2: Add a record to user meta for each liked post

Following the click, the code checks if a record has already been inserted into user meta. If not, it adds a new record of the favourite. If it has, it does nothing.

What I like about this system, is it is easy to query and generate statistics down the road as it is not tied up in serialised arrays. What I don’t like is that if you have the user liking lots of posts you could hugely increase the number of records in the user meta table.

// Check if already favourited the post in User Meta
// Not sure how you would do this with WP_Query or WP_User_Query, any suggestions?

$favourited = WP_Query($args)

if ($favourited->post_count == 0) {
  // Add user meta with favourite
  add_user_meta($userid, '_favourite_episode', $postid);
}

So to conclude, what I am asking essentially is what is best practice here. Is it either:

  • Having one large serialised array in a single meta key/value pair
  • Having many meta/key value pairs with an integer in the meta value
  • Is there another option I haven’t considered?

EDIT: Following the answers given, I have decided that creating a custom table is the best way forward. I have found this tutorial that does pretty much what I want to do and in a much more extendable way so that I can add other actions as well as just ‘favouriting’.

http://code.tutsplus.com/series/custom-database-tables–wp-33839

2 Answers
2

You forgot option 3 – Add a special table in which the pair (user,post id) will be the index. Ok I am not a MySQL person so maybe it is too extreme, but maybe having two tables one with users as index and one with posts will be even better.

The thing about performance is that there are rarely absolute solutions for everybody at anytime, and the “best” depends on your actual usage pattern, not the theoretical one. Or in other words, you are doing early optimization here.

While option 2 seems to be faster for this specific information it will make the meta tables bigger and therefor is likely to slow down all requests to those tables (therefor the suggestion for option 3 which is very similar but do not impact other queries).

Another issue you ignore here is the cost of data insert/update. This operation is much slower then a read and can not be cached. If you are going to have many likes at the same time, with option 2 you will lock the tables and requests will need to wait for others to complete and each insert will be slower while option 1 is likely to end in data corruption under naive implementation (two changes in the same time, at best only one of them will have impact).

And then you should take into account what kind of caching will you do. With a good caching scheme the read time is a non issue.

To conclude I wish that you will find this to be an actual problem that needs to be solved, till then just write proper api to access/change that data to hide the implementation detail so if it will become a problem you will be able to change the implementation without impacting the rest of your code.

Leave a Comment