News feed database design as in Facebook

Question

How would make a news feed "friendly" database design, so that it wouldn't be extremely expensive to get all of the items (query) to put in the news feed? The only way I can think of would involve UNIONing nearly every table (representing groups, notes, friends, etc) and getting the dates and such, that just seems like it'd be a really expensive query to run for each user, and it'd be pretty hard to cache something like that with everyone's being different.

Answer 1

Firstly, consider doing a performance prototype to check your hunch that the union would be too expensive. You may be prematurely optimisizing something that is not an issue.

If it is a real issue, consider a table designed purely to hold the event feed data, that must be updated in parallel with the other tables.

E.g. when you create a Note record, also create an event record in the Event table with the date, description, and user involved.

Consider an indexing the Event table based on UserId (or UserId and Date). Also consider clearing old data when it is no longer required.

This isn't a normalised schema, but it may be faster if getting an event feed is a frequent operation.

Answer 2

It's hard to answer this question without a schema, but my hunch is that a UNION involving 10 or more properly indexed tables is nothing:
A typical LAMP application like wordpress or PHPBB runs more than 10 queries per pageview without problems. So don't worry.

Answer 3

UNION = expensive, because the complete result set is subject to a DISTINCT operation. UNION ALL = cheaper, because it is effectively multiple queries for which the results of each are appended together.

It depends on the data volume, or course.

The main driver of efficiency would be the individual queries that are unioned together, but there's no reason why selecting the most recent (say) 10 records from each of 10 tables should take more than a small fraction of a second.

Answer 4

Discussion about implementing social activity streams here: http://stackoverflow.com/questions/202198/whats-the-best-manner-of-implementing-a-social-activity-stream