DB structure for Twitter home/Facebook wall?

Question

Basically a live feed of all your friends' recent posts.

In a stupid sort of approach I think I'd start by building a query like:

SELECT * FROM tblposts WHERE userid=friend_id_1OR userid=friend_id_2...... and so on

Where friend_id_% is the userid of a friend from your friends list. But this must be a very inefficient way of doing it, right? Is there any faster way of doing this in MySQL? Maybe some clever DB schema?

(I know FB uses Hadoob but I'm not experienced enough to go that far :( )

Answer 1

If you have a list of IDs that you want to query then you should use IN:

SELECT * FROM tblposts WHERE userid IN (friend_id_1, friend_id_2, ...)

But in this case I think you can use a join instead.

SELECT * FROM tblposts AS T1
JOIN (
   SELECT friendid             -- I just made up this query.
   FROM friends                -- You need to replace it with the
   WHERE userid = 'youruserid' -- real query to get someone's friends.
) AS T2
ON T1.userid = T2.friendid

Answer 2

Sorry to bump, but I think the query above implies that the friends table has a lot of redundant records? (x is friends with y, y is friends with x; 2 records?)

If friends table has fields sender_id receiver_id and request_status instead, can the query above be modified in a way that it returns friends list against youruserid, i.e. select value of receiver_id if sender_id==youruserid and vice versa so we get one list of all friends of youruserid? I can't think of a better table to avoid duplication.