Storing like count for a post in MySQL

Question

Is it a good idea to store like count in the following format?

like table:

u_id | post_id | user_id

And count(u_id) of a post?

What if there were thousands of likes for each post? The like table is going to be filled with billions of rows after a few months.

What are other efficient ways to do so?

Answer 1

In two words answer is : yes , it is OK. (to store data about each like any user did for any post).

But I want just to separate or transform it to several questions:

Q. Is there other way to count(u_id)? or even better:

SELECT COUNT(u_id) FROM likes WHERE post_id = ?

A. Why not? you can save count in your post table and increase/decrease it every time when user like/dislike the post. You can set trigger (stored procedure) to automate this action. And then to get counter you need just:

SELECT counter FROM posts WHERE post_id = ?

If you like previous Q/A and think that it is good idea I have next question:

Q. Why do we need likes table then?

A. That depends of your application design and requirements. According to the columns set you posted : u_id, post_id, user_id (I would even add another column timestamp). Your requirements is to store info about user as well as about post when it liked. That means you can recognize if user already liked this post and refuse multilikes. If you don't care about multilikes or historical timeline and stats you can delete your likes table.

Last question I see here:

Q. The like table is going to be filled with billions of rows after a few months. isn't it?

A. I wish you that success but IMHO you are 99% wrong. to get just 1M records you need 1000 active users (which is very very good number for personal startup (you are building whole app with no architect or designer involved?)) and EVERY of those users should like EVERY of 1000 posts if you have any. My point here is: fortunately you have enough time till your database become really big and that would hurt your application. Till your table get 10-20M of records you can do not worry about size and performance.