I wonder how Facebook uses the Mutual Friends feature.

Question

I am currently developing an application that allows students to manage their courses, and I really do not know how to create a database for a specific function. The client wants, like Facebook, that when a student displays a list of people who are currently on a particular course, people with the most common courses with a registered user are displayed first. Almost the same as the Facebook function "Suggestions of friends" with an additional filter.

As an additional feature, I would like to add a search function to allow students to search for another and display first in the search results people with most reciprocal courses with a registered user.

I am currently using MySQL, I plan to use Cassandra for some other functions, and I am also using Memcached to cache results and Sphinx for searching.

Thanks.

-

Application developed in Python, BTW

And I forgot to mention that the standard approach (using a good MySQL query to compute all of this with an ORDER BY clause) is too slow. Since reading is much more common than reading, I would like most of the logic to be executed once when the people ↔ course relation is added.

I thought about updating the counter of reciprocal courses specific to one tuple (user, course), which will be increased for all users of the course when a registered user joins a new course (or decreases when he leaves it).

Answer 1

If you already have a solution, but the problem is just the speed of this request, try to do it earlier. When the friendship of friends changes, repeat the task, which calculates these things and saves all the results. Do not scold him as a result of a request when you need a result so quickly. Do such expensive things only once and do them before the request is made.

Answer 2

Say you have a table named Users , and the main key is UserID . Then you have a table called Friends with two columns called UserID (PK) and FriendUserID .

Let's say you have 2 users, 20 and 50.

When 20 adds 50 as a friend, the application adds a new line:

 INSERT INTO `Friends` (`UserID`, `FriendUserID`) VALUES (20, 50) 

and when 50 confirms the friendship, you add another line with the values:

 INSERT INTO `Friends` (`UserID`, `FriendUserID`) VALUES (50, 20) 

If you want to find mutual friends between 20 and 50, simply:

 SELECT `UserID` FROM `Friends` AS `A`, `Friends` AS B WHERE `A`.`FriendUserID` = 20 AND `A`.`UserID` = `B`.`UserID` AND `B`.`FriendUserID` = 50 

Answer 3

I would break it as (2) queries and find the intersection in Python:

 #Query 1 - Get the user friends SELECT friend_id FROM friends WHERE user_id = 'my user id' #Query 2 - Get the users enrolled in the course SELECT student_id FROM course_enrollment WHERE course_id = 'course id' 

Then find the intersection in Python. Then you can let the database do caching, etc. Without any connections to slow down.