I have a social network site, around 50,000 users so my mysql DB is pretty large, there is a table that has 2 record for every friendship made between 2 users, so this table is approaching around a million rows.
What I am wanting to do is show recent updates from friends, the code will have to get all blog post, status updates, and photos.
If you have seen facebook.com or the newer style myspace.com you will know what I am talking about.
I know there is several ways to do this but I am looking for suggestions because performance is very important with the size of my site.
What in your opinion would be the best way to do this? 1 idea I had was everytime a blog, bulletin, status update is posted, to also insert into a new table that will have a these fields "userID" "content_type" "date" and for content type it could mark the item as being one of these, blogs, bulletins , status then I could do a mysql query on this table to find all items within a date range that have a userID that is in your friends list, the problem I see with this route for performance is, wouldn't I then have to do a while statement on this query, then do another query for each result to get the appropriate data like subject and user id for each post...is there a better way to do this, in the end all I am looking to do is show your friends recent updates like all the big social networks do.
If you were doing this and you had a large userbase and performance was a must how would you go about setting it up?
ANY AND ALL HELP AND INFO is very much appreciated on this issue
