Facebook-style news feed

[broshnat]

Hi everyone, we have just launched a new site and I want to incorporate a Facebook-style news feed for users. The site is based around gaming and users can follow games that they are interested in so we have a "follows" table which matches user_id to game_id. There is a third column in this table that has a value between 1 and 10 and represents the user interest level in that game.

 

We then have an events table which lists a variety of different events relating to the games - when a news story is added, when the game is updated, new screenshots, videos etc. We have a column in this table with a value of 1-10 that we assign a value of newsworthiness to.

 

So you can probably see where I want to go - for a given user we want to return a list of events (which we can then parse into a feed) - which seems like a simple join but there are a few complexities:

 

• We already have over 400,000 "follows" and this will grow fast into the millions.
  
• Similarly, there will be thousands of events being added per day so a large number of writes to the events table as well as a large number of reads everytime we need to pull a feed for someone.
  
• Finally, rather than just list every event relating to an item you follow, I want to weight them by a combination of newsworthiness and interest level.
  

 

The main issue really is one of speed and efficiency. I know of many solutions we can engineer quickly but they all seem to require huge queries and large sets of data and table joins which I can see getting messy. Also, both tables will have records added constantly which obviously isn't ideal if you are constantly reading as well.

 

Obviously a feed only needs to be transient - we only need to look at the most recent events - but clearly a user following 1000 games will have many more recent events than a user following 2 games so I need a system that will work for both. I had thought about producing a temp table of recent events, say every 10 minutes and reading from that but we want things to show up instantly ideally, not have to wait 10 mins for the next cron job to run.

 

So I'm wondering if anyone has experience at anything like this or has any neat ideas on how it could be run efficiently?

[ignace]

I don't see the issue here. We have tables with 25M rows and increasing as we speak, it's size is in the GB's and queries return their results really fast, I believe that last time I checked it was something along the lines of (min: 0.016ms avg: 0.032ms max: 0.078ms) if my memory serves me well, could be something entirely differently though.

 

What I would do is when the user signs in grab all games he is interested in and cache it in his session variable (or a cookie that persists even if the user is no longer signed in, so that you can still display game news, though you will have to regularly sync this whenever he starts following a new game or unfollows one).

 

SELECT game_id
FROM follows
WHERE user_id = ?

 

Then when you want to grab all news:

 

SELECT foo, bar, baz
FROM events
WHERE game_id IN(?)

 

Both queries should run really fast even if both have 25M rows and more. Even if your write/read operations become to heavy you can still replicate your data in a master-slave setup. Where the master receives all writes and you read from the slaves. The master regularly updates the slaves with batch queries.

[ignace]

Sorry I could no longer edit my original post but that should be:

 

SELECT game_id, interest_level
FROM follows
WHERE user_id = ?

 

and

 

SELECT foo, bar, baz
FROM events
WHERE (game_id = ? AND interest_level >= ?) OR (..)
ORDER BY newsworthiness DESC