This may be a hairy question but. Say I have
Followers:
-user_id
-follower_id
Activities:
-id
-user_id
-activity_type
-node_id
Pulling a users activity is fairly easy. But what is the best way to get a followers activity? A subselect? It seems like it is incredibly slow as users get more and more followers. Any ideas to speed this up?
Also, on a more conceptual level. How does the grouping work. Is it all done with a single query? Or is all the activity data pulled in and then sorted and grouped on the PHP side?
Users X, Y and Z did Activity A User J did 3 of Activity B
Subselects are often slower than JOINs, but it really depends on what exactly you're doing with them. To answer you main question, I would get follower data with a JOIN:
SELECT * FROM followers f
LEFT JOIN activities a ON f.follower_id=a.user_id
WHERE f.user_id=$followedPerson
That's assuming that the followers table represents a user with user_id, and someone who is following them with a follower_id that happens to be a user_id in the users table as well.
It won't ever be incredibly slow as long as you have an index on followers.user_id. However, the amount of data such a query could return could become larger than you really want to deal with. You need to determine what kinds of activity your application is going to want to show, and try to filter it accordingly so that you aren't making huge queries all the time but only using a tiny fraction of the returned results.
Pulling data out and grouping it PHP side is fine, but if you can avoid selecting it in the first place, you're better off. In this case, I would probably add an ORDER BY f.follower_id,activity_date DESC, assuming a date exists, and try to come up with some more filtering criteria for the activity table. Then I'd iterate through the rows in PHP, outputting data grouped by follower.