More efficient way to get updates from friends

[virruss]

Hi guys,

 

I'm developing a website which allows people to connect and follow each other's activity (like Twitter, for example).

 

To simplify everything, let's say I only have 2 tables:

 

1. Followers

 

id | follower_id | id_to_follow

------------------------------------

 

2. Activity

 

id | member_id | message | time

-----------------------------------------

 

Let's say John is following Jane and Bob. I want to create a "news" page and display the last 20 messages from Bob and Jane, chronologically.  For small numbers, I'd do something like this:

 

Select everything from the Activity table, check for every entry if the member is a friend of John's (in the Followers table) and, if so, display the message, ORDER BY `id` DESC.

 

But, this is very inefficient, I guess, for larger numbers (I can't even think about how many queries would take to do this on a site like Twitter...).

 

Any ideas of how to do the same thing more efficiently?

 

Thank you.

[shlumph]

Just make sure you're using a single query using joins, and also paginate the data (for instance, 25 records per page) and you should be ok

[virruss]

Thanks, man!

 

I think this is a great place for me to start designing the structure. I didn't know that using JOIN is more efficient. I've managed to do the job for this simple example.

 

But is it ok to search another table for a photo, separately, for each user (this means about 20 more queries per page), or can I do that in the same query too? I'm not sure how complex can a single query get.

 

Do you have an example so I can study it instead of being abstract? Thanks a lot.

[ignace]

I didn't know that using JOIN is more efficient

 

It isn't. But for a start-up things shouldn't give you a problem anytime soon after all it took until most Twitter users complained from lagg that they finally made the switch to a NoSQL alternative.

[shlumph]

But is it ok to search another table for a photo, separately, for each user (this means about 20 more queries per page), or can I do that in the same query too?

It's *much* more efficient to get their photo in the same query. Queries can get pretty complex

 

Websites like twitter, facebook, amazon, etc. structure their databases in a non-relational way (NoSQL), so that they can create a single query without any joins to get all the information they need. I wouldn't worry about going to this extent. But using joins is far more efficient than using multiple queries.

 

Single Query < Single Query with Joins < Multiple Queries

[fortnox007]

structure their databases in a non-relational way (NoSQL)

 

Does this mean they put everything in 1 huge table?

[shlumph]

Does this mean they put everything in 1 huge table?

 

There's a lot of redundant fields between the tables, instead of having foreign keys. I'm no expert on NoSQL, though