Store number of likes of a post in MySQL

Question

I am creating an application which will enable users to create posts and also like posts. I am using MySQL to store all the data.

I have the tables users and posts. I want to prevent the situation where a user likes the same post more than one time.

How should I store the number of likes of each post in an efficient way?

Should I have the like_count column in the posts table and also have another table "post_likes" where I store the ID of the user and the ID of the post?

This way I would have to check the post_likes table to see if a user has already liked before incrementing the count on the posts table.

And how do I avoid concurrency issues?

Another option would be to have the post_likes table and insert a new record every time a user likes a post. I wouldn't store the likes count in the posts table but calculate it every time with a select count query.

But is this efficient?

Can the process of checking if the user has already liked be included in one SQL statement and access to the database?

Or should the backend of my app take care of that check?

Answer 1

You will need to have an individual record whenever a user likes a post. For this purpose, we create the post_likes table like so:

create table post_likes(
    id int primary key auto_increment,
    post_id int not null,
    user_id int not null,
    foreign key (post_id) references posts(id) on delete cascade on update cascade,
    foreign key (user_id) references users(id) on delete cascade on update cascade,
    unique (post_id, user_id)
);

That unique constraint ensures that the user does not like a post twice and it's also increasing performance. You can count the number of likers of a post via:

select count(*)
from post_likes
where post_id = 1234;

and you should check whether this is quick-enough for you. You should not prematurely optimize this before your realize that a performance issue actually occurs. Because if you start storing the count of likers in a different table, then you will have to always sync the data and worry about data integrity. Choose the approach outlined above and do not perform optimizations above it until there are actual performance issues.

You might also decide to stress-test by generating lots of records and issuing many reads and writes at the same time to see when this is starting to get slow.

As about concurrent access, in general MySQL handles that pretty well, so I presume you will not have issues with concurrent access. You will still need to perform the testing though.