3

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

3

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.

  • Hi Mark, thanks for your answer. Would there be much additional cost in having to write custom queries to the "wp_likes" table instead of using the built in get_post_meta and get_user_meta? – Alex Apr 3 '16 at 16:25
  • One problem with adding a table is that you need to implement the object caching layer by yourself, while when using the user and post meta table you get it "for free". And for a site with active users like you describe object caching is a must. – Mark Kaplun Apr 3 '16 at 16:29
0

Option 1 is not the best choice because serializing data means you must parse your SQL result to be readable and thus you can't take any advantages of SQL queries.

Option 2 would be the easiest way but if you have a lot of users that are liking a lot of posts then it starts to pollute user meta table.

Option 3 what i would do, is create relational table between posts and users. Using relation table would be the easiest because then you can use SQL to do a lot of logic for you. For example you don't need to count how many likes one post has in PHP but instead run a query against SQL that does it for you and returns the result. Meaning it will be good for performance and if you uninstall the plugin then all you have to do is remove the table, simple and clean.

  • Hi Martin, thanks for your answer. Just so I'm clear with the relational table idea, you would have a "wp_likes" table with columns ID, user_id, post_id and just insert a new record whenever a like happens? This sounds interesting, but would having custom queries cause an issue in terms of scalability? – Alex Apr 3 '16 at 16:14

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.