CODEWITHSUNDEEP
mysql
user696495
user696495

Reputation: 149

SQL query with mutual user relationship

I'm making a SNS that users can follow each other. If user A follows user B and user B also follows user A, they become friends.

Also consider that some popular people(like movie stars) may be followed by hundreds of thousands times, but a user can follow 1000 people max.

So given the table below, what is the best SQL query to fetch all friends' ids of user 1?

PS: I'm using MySQL 5.5.

Here is what I have done so far:

SELECT followee_id AS friend_id FROM follow
    WHERE follower_id = 1 AND
        followee_id IN (SELECT follower_id FROM follow
                            WHERE followee_id = 1);


CREATE TABLE follow
(
    follower_id     INT UNSIGNED NOT NULL,
    followee_id     INT UNSIGNED NOT NULL,

    PRIMARY KEY (follower_id, followee_id),

    INDEX (followee_id, follower_id)
);

Upvotes: 2

Views: 1240

Answers (1)

StuartLC
StuartLC

Reputation: 107267

Assuming that by 'best' you mean most performant, and given that a following must be mutual in order to meet your 'friend' criteria:

A filter using followee_id will hit your index better than a filter on follower_id 

select 
    me.follower_id
from 
    follow me inner join 
    follow you 
  on 
      me.follower_id = you.followee_id
      and me.followee_id = you.follower_id
where 
    me.followee_id = @user

(although note that RDBMS's like MSSQL will default to using your Primary Key as a clustered index, in which case its much of a muchness really.)

Upvotes: 3

Related Questions