Friend Tape Algorithm

Question

Database

There are 3 tables:
1) Users (Users: UID)
2) Posts written by users (Posts: PID, UID, text, date)
3) Friendships (Friens: UID, FriendUID)

Objective: Quickly show all posts of a user's friends, sorted by date (friendline).

It gets complicated by a few points:
1) users can have a bunch of friends (thousands)
2) users can have a bunch of posts (thousands too)
3) they might want *unexpectedly* to see posts from 1000 to 1010

in their friend feed. :
0) Google does not find anything for the query "algorithm friendline" and "algorithm friendline".
1) A simple JOIN will not work, as it will slow down the sorting of all posts by date.
2) You can add a label (friendline: UID, PID, date) with an index (UID, date) and quickly search for it. But then adding / removing a friend from 1000 posts will make her think for a long time.

Maybe someone knows where to dig?

Answer 1

Usually such things are put in a separate table (call it a cache or something else), and do not delete from it, but mark it as deleted. Well, sharding for it if there is too much data.

Answer 2

1. The insertion of 1000 records will happen instantly, because you do not need to store all the data, but only the IDs.
2. Experiment with queries:
  2a. choose IDs of all friends
  2b. select all friends by ID (yes, yes, no matter how funny it sounds)
  2c. select feed activity
Sometimes these 3 requests are faster than cham one join
3. "they may want *unexpectedly* to see posts from the friend feed from 1000 to 1010." - score
4. If the messages are not changeable, then there is no point in sorting by date, I hope there is a primary numeric key? it is always larger in the latest entries
. That's all that immediately came to mind.

Answer 3

If you are afraid to make the user wait, process option 2 through the queue.

Answer 4

SQL? wtf??? NoSQL!!!11