Likes like on a toaster in a database?

Question

PHP

Likes to answers are stored in the database in the form:
otvet_id | user_id
Further it is clear the user likes, we check if there is a ratio - we remove the like, if not - we put a like.
Soap 1: the size of the table of correspondences. Here is a picture from one forum:

In total, there are 500 thousand messages in the section, let's say 2 likes on average for each - there are already a million lines of correspondence. This is only one section, and there are many sections.
Thought 2: This is the inscription "You like" i.e. we have already checked that the user's like is here. But it's one thing to pull the millionth table when they clicked on the like button, and the second is just every time the page is displayed to a registered user. DB going crazy?
Thought 3. And after all, you won’t cache the page because of these likes)) Well, or prohibit liking old posts and cache them?

Answer 1

It is solved quite simply, each answer has a likes counter that is updated either with each new like, or in the background with some kind of cron.
It turns out not very consistent, but waiting for the forum is not very critical.

Answer 2

I don’t know, I would just store the date of the like in the table with likes and throw off the old likes in a month and just a counter for a quick withdrawal for the record. If you need to display a list of likes, then display "Liked by Alex, Max and Fedor and another 100500 people"

Answer 3

What is the problem? BD ALREADY slows down? Requests take longer than 1 second?
2-3-4, and even 10 million records - nothing, if the selection is on indexes.
Do it, check it, see what specifically slows down. There will be more PHP slows down than a database with a selection by indexes.

Answer 4

In total, there are 500 thousand messages in the section, let's say 2 likes on average for each

1 and 2) If there are few likes (10, 100), then you can store a list of users who liked to store in the message itself. Thus, there will be no additional tables and additional queries.
3) It is cached, and the state (button and number) is updated on the client or on the server before sending (there can be several caches and at different levels, some caches rely on others ...)

Answer 5

Take postgresql
For each post, store a field with an array of id of all likes
www.postgresql.org/docs/9.1/static/arrays.html
and a field with a like counter.
To prevent likes, check the presence of the user id in that array several times.
https://stackoverflow.com/questions/11231544/check...
Adding users to an array is also easy stackoverflow.com/a/11008296

Answer 6

1. Caching on the PHP side
2. Competent indexes
3. Partitioning
4. Muscovy tuning