How are social network newsfeeds stored?

[dannyb785]

Hi

 

I can't seem to find an answer anywhere else so I hope someone can give some insight. I'm not looking for actual code, just an example of 2.

 

I've written a few social networks in the past, mostly for fun. But the newsfeed part always tripped me up. I started off by making a database table called "Newsfeed_Post" and each row would have a set of info that explained what the newsfeed post was for(events, pictures, etc), which user did it, etc. 

 

But then when I got to the part where I wanted to grab the last 20 posts that were relevant to any particular user, that's where I'm worried about my mysql queries. I understand about JOIN's and whatnot, but if I wanted the option to show the newsfeed updates for users who were a: my friend b: not suspended, c:not hidden(since you can hide a friend's post), etc, that would produce quite a few WHERE conditions, which were very resource-heavy.

 

So all I'm asking about is the concept behind popular newsfeed, we can maybe say facebook for example. I'm not asking how facebook does theirs(obv we don't know) but I'm asking how could facebook(or other social networks) do a newsfeed system that doesn't include a whole bunch of WHERE conditions? Or is it just not possible?

 

Or was my whole idea of having a separate "Newsfeed_Post" table a bad idea? If so, please explain why it's a bad idea.

 

I'm just looking for concepts/idea and possibly the pros/cons for each, if you wish to explain them.

[Jessica]

If you have indexes on the tables of the right kind, it shouldn't be that big a deal.

[dannyb785]

If you have indexes on the tables of the right kind, it shouldn't be that big a deal.

 

That doesn't answer any part of my question. I can tell that you enjoy being vague in your posts, but why even reply if you're not going to help?

[DavidAM]

That doesn't answer any part of my question. I can tell that you enjoy being vague in your posts, but why even reply if you're not going to help?

Hey, be nice to Jessica, she's my friend!

 

... that would produce quite a few WHERE conditions, which were very resource-heavy. ...

It answers the question implied by the part of your statement that I highlighted there. INDEXES are the key to fast queries (and low CPU resource use).

 

 

In a really big site, if the sheer number of users starts bogging down your indexes, you could create special cross-reference table(s) to speed up queries. But you don't do that until you have a solid database design and application design, and know what kind of problems you will run into. And you have to do a very good job of maintaining the references. It's probably easier and cheaper to just throw hardware -- disk space, memory, additional servers -- at the problem.

 

As for Facebook, they use multiple database servers with replication. The other night, my daughter and I were sitting on the couch, side-by-side, using our separate notebooks, making changes to a family group. And while we could each see our OWN changes on the screen, neither of us could see the other's changes right away (measured in minutes). The replication was running kind of slow.