Let's assume that you are right, that their tables look something like this:
Friend User Action
havefriendid id id
isfriendid date
userid
Basically what you are saying is that you would do the following (in pseudocode).
result = select * from Friend where havefriend = yourid
foreach (result as friend) {
result2 = select * from User where id = friend.isfriendid
foreach (result2 as user) {
result3 = select * from Action where userid = user.id
foreach (result3 as action) {
do a lot of things
}
}
}
Well, that is not the way it is done. If they have those tables they would use one query with joins instead of thousands of queries to get the actions. Something like this:
result = select columns from Friend join User on friend.isfriendid = user.id join Action on user.id = action.userid where friend.havefriendid = yourid order by action.date
foreach (result)
do a lot of things
That is probably closer to the truth. But it will return to many rows, I assume that it only returns one result per friend. And it is still to heavy on the database. Maybe they do something like this instead:
Friend User Action
havefriendid id id
isfriendid lastchangedate date
lastchangeheadline userid
Whenever the user do an action the date and headline is transferred to the user. Then you don't have to worry about the action table at all. Something like this would do:
select * from friend join user on friend.isfriendid = user.id where friend.havefriendid = yourid order by lastchangedate
It is even possible that they insert it into the friend table, then they don't have to use any expensive join.
Of course there are a few things I haven't discussed here. Indexes, hardware, clustered servers and so on of course are key things to solve heavy database traffic. And to be honest, it is not much to join only 200 columns, that is still a small database. The problem is that there are a huge amount of people doing the same at the same time.
