How are application like twitter implemented?

Question

Suppose A follows 100 person,

then will need 100 join statement,

which is horrible for database I think.

Or there are other ways ?

Answer 1

Why would you need 100 Joins?

You would have a simple table "Follows" with your ID and the other persons ID in it...

Then you retrieve the "Tweets" by joining something like this:

Select top 100 
    tweet.* 
from 
    tweet 
inner join 
    followers on follower.id = tweet.AuthorID 
where 
    followers.masterID = yourID

Now you just need a decent caching and make sure you use a non locking query and you have all information... (Well maybe add some userdata into the mix)

Edit:

tweet

ID - tweetid
AuthorID - ID of the poster

Followers

MasterID - (Basically your ID)
FollowerID - (ID of the person following you)

The Followers table has a composite ID based on master and followerID It should have 2 indexes - one on "masterID - followerID" and one on "FollowerID and MasterID"

Answer 2

The real trick is to minimize your database usage (e.g., cache, cache, cache) and to understand usage patterns. In the specific case of Twitter, they use a bunch of different techniques from queuing, an insane amount of in-memory caching, and some really clever data flow optimizations. Give Scaling Twitter: Making Twitter 10000 percent faster and the other associated articles a read. Your question about how you implement "following" is to denormalize the data (precalculate and maintain join tables instead of performing joins on the fly) or don't use a database at all. <-- Make sure to read this!