PHP / MySQL activity (ala facebook)

Question

It might be a hairy question, but. Say I have

Followers:
-user_id
-follower_id

Activities:
-id
-user_id
-activity_type
-node_id

Pulling user activity is pretty simple. But what is the best way to get followers active? Subtitle? This seems to be incredibly slow as users get more and more subscribers. Any ideas to speed this up?

Also at a more conceptual level. How grouping works. Is everything done with a single request? Or are all activity data pulled in and then sorted and grouped on the PHP side?

Users X, Y, and Z performed operation A; User J made 3 of Activity B

Answer 1

, JOIN, , . , JOIN:

SELECT * FROM followers f
LEFT JOIN activities a ON f.follower_id=a.user_id
WHERE f.user_id=$followedPerson

, user_id, , follower_id, user_id .

, followers.user_id. , , , , . , , , , .

PHP - , , . , , ORDER BY f.follower_id,activity_date DESC, , , . PHP, , .

Answer 2

, . , 100 , .

- , , , . , A , B C, A - :

record 1: "I did this" log for user A
record 2: "My friend did this" log for user B
record 3: "My friend did this" log for user C

, . , . , , (.. 1 ).

:

-id
-user_id  (user who activity log this is)
-action_user_id  (user who took the action, or null if same as user_id)
-activity_type
-date

:

SELECT * from activity_log WHERE user_id = ? ORDER by date DESC LIMIT 0,50

, , . , , .

Answer 3

I don’t know if I understood correctly what you need, but I would try this choice, if I'm right, you should get all the activity for all the followers #USERID#

SELECT a.* FROM Activities AS a 
INNER JOIN Followers AS f1 
ON a.user_id = f1.follower_id
WHERE f1.user_id = #USERID#