CODEWITHSUNDEEP
sqlpostgresql
BVtp
BVtp

Reputation: 2480

Optimizing query for finding friends

I have a table which represents relations between pairs of people.

user_relating | user_related | relation_type 
--------------------------------------------
     1        |       2      |      1      --> means user 1 follows user 2

I need to find all friends for a specific user X. Friendship means that two people follow each other. So if user A follows users B, C. And users B, C follow A, then B, C are friends of A.

I've written this query :

SELECT users.* -- user's followers
FROM users
JOIN user_relations rel
ON users.id = rel.user_relating AND user_related = 2

INTERSECT

SELECT users.* -- who the user follows
FROM users
JOIN user_relations rel
ON users.id = rel.user_related AND user_relating = 2;

But I think it's inefficient. Is there a more optimized way to get this done?

I've tried doing something like this :

SELECT DISTINCT f.*
FROM users f
JOIN user_relations u1 
    on f.id = u1.user_related
JOIN user_relations u2 
    on f.id = u2.user_relating

WHERE u1.user_related = 2 
   or u2.user_related = 2;

it seems much more efficient judging by the EXPLAIN ANALYZE (though I only have a really small table, like 10 rows so I'm not sure it's a good measurement).

But the problem here is that it returns the user in question too. Meaning, if I want friends of User B , then this query returns User B together with all his friends. Can I somehow exclude User B from the query result?

And, as stated formerly, I would be glad to receive some ideas for the most optimized and efficient way to do this kind of queries.

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Probably the most efficient method is:

select ur.*
from user_relations ur
where ur.user_relating < ur.user_related and
      ur.relation_type = 1 and
      exists (select 1
              from user_relations ur2
              where ur2.user_relating = ur.user_related and
                    ur2.user_related = ur.user_relating and
                    ur2.relation_type = 1
             );

And for performance, you want an index on user_relations(user_relating, user_related, relationship_type).

That said, this is similar to your version with the join, but does not require removing duplicates.

If you have lots of different relationship types, then an index on that column could also help.

EDIT:

If you have a particular user and want their friends:

select (case when user_relating = 2 then user_related else user_relating end) as user_friend
from user_relations ur
where ur.user_relating < ur.user_related and
      ur.relation_type = 1 and
      exists (select 1
              from user_relations ur2
              where ur2.user_relating = ur.user_related and
                    ur2.user_related = ur.user_relating and
                    ur2.relation_type = 1
             ) and
      2 in (ur.user_relating, user_related)

Upvotes: 1

Related Questions