Performance for select friends of friends sql (but not my friends)

Question

I want to get the friends of my friends who are not my friends. I have a table like this: userFriends(idUser, idUserFriend)

I was thinking about something like this:

select distinct idUserFriend from userFriends where idUser in
(select idUserFriend from userFriends where idUser = 1)
and idUserFriend not in (select idUserFriend from userFriends where idUser = 1)

But I am not sure if its so slowly or if it can be faster in another way.

Any views on this?

then what is question? performance is question? – Fahim Parkar Jun 21 '12 at 11:06 — Fahim Parkar, Jun 21 '12 at 11:06
Yes it is a performance question. – Alvaro Jun 21 '12 at 11:09 — Alvaro, Jun 21 '12 at 11:09

wildplasser · Accepted Answer · 2012-06-21T12:16:45.733

3

SELECT DISTINCT
    two.idUserFriend
FROM userFriends one
JOIN userFriends two ON one.idUserFriend = two.idUser
WHERE NOT EXISTS (
        SELECT * 
        FROM userFriends nx
        WHERE nx.idUser = one.idUser
        AND nx.idUserFriend = two.idUserFriend
        )
AND one.idUser = 1
        ;

The same can be accomplished by a "NOT IN" construct:

SELECT DISTINCT
    two.idUserFriend
FROM userFriends one
JOIN userFriends two ON one.idUserFriend = two.idUser
WHERE two.idUserFriend NOT IN (
        SELECT  nx.idUserFriend
        FROM userFriends nx
        WHERE nx.idUser = one.idUser
        )
AND one.idUser = 1
        ;

And there is the "EXCEPT" version:

SELECT DISTINCT
    two.idUserFriend
FROM userFriends one
JOIN userFriends two ON one.idUserFriend = two.idUser
WHERE one.idUser = 1
EXCEPT (
        SELECT  nx.idUserFriend
        FROM userFriends nx
        WHERE nx.idUser = 1
        )
        ;

edited Jun 21 '12 at 12:16

answered Jun 21 '12 at 11:27

wildplasser

43,142
8
66
109

The frist one gives a different result than the 2nd one. And the 2nd one needs a distinct in onder not to get duplicate results. THe 2nd one is the good one anyway :) – Alvaro Jun 21 '12 at 11:44
Is the faster then that the one i used at my question? – Alvaro Jun 21 '12 at 11:45
Yes, nulls could make a difference. Or did I miss something? Oops: bad correlation name, I'll fix it. TNX. I don't know if it is faster, but is probably more correct. (after the fix, that is ;-) I'll add the distinct to the outer query. – wildplasser Jun 21 '12 at 11:46
I don't care about speed. I care about correctness. I don't know mysql, but I heard that `WHERE ... IN (` subqueries perform badly in mysql, so the `NOT EXISTS` variant would be faster. (or the EXCEPT-variant) – wildplasser Jun 21 '12 at 11:54

score 1 · Answer 2 · edited May 23 '17 at 12:27

1

Use joins to make the comparisons:

SELECT
    b.idUserFriend
FROM 
    userFriends a
INNER JOIN
    userFriends b ON a.idUserFriend = b.idUser
LEFT JOIN
    userFriends c ON a.idUser = c.idUser AND b.idUserFriend = c.idUserFriend
WHERE
    a.idUser = 1
    AND c.idUser IS NULL
GROUP BY
    b.idUserFriend #eliminate duplicates

Typically, it is preferable to use JOINs over subqueries as they will utilize indexes on related fields for comparisons. A subquery on the other hand, will execute for each row returned, even if it's a non-correlated subquery (MySQL).

More info on JOINs vs Subqueries

edited May 23 '17 at 12:27

Community

1
1

answered Jun 21 '12 at 11:11

Zane Bien

22,685
6
45
57

This one gives me all the friends friends. Even the ones i already have as friends. So this is not what i am looking for. – Alvaro Jun 21 '12 at 11:35
Oops! Sorry. Fixed, but still managed to avoid subqueries. Try it now. – Zane Bien Jun 21 '12 at 11:46
Ok, it just needs a distinct in order not to get duplicates. But.. is it faster than the one proposed by wildplasser?? – Alvaro Jun 21 '12 at 11:49
Are friend relationships entered into the table twice? (e.g. `5->1`, `1->5`) And you'll have to benchmark that for yourself. Use `EXPLAIN` to view information about the execution plan. – Zane Bien Jun 21 '12 at 11:52

score 1 · Answer 3 · answered Jun 21 '12 at 11:12

1

select myFriend.idUserFriend from userFriend me inner join userFriends myFriend 
on me.idUserFriend = myFriend.idUser and myFriend.idUserFriend != me.idUserFriend 
where me.idUser='MyUserId';

answered Jun 21 '12 at 11:12

manurajhada

5,284
3
24
43

Kshitij · Answer 4 · 2012-06-21T11:57:40.207

1

Try this -

SELECT UF2.idUser, UF2.idUserFriend 
FROM USERFIRENDS UF2 INNER JOIN 
(
    SELECT idUser, idUserFriend
    FROM USERFRIENDS UF1
    WHERE UF1.idUser =1 -- this query gives my friend
) MYFRIENDS ON 
      UF2.idUser=MYFRIENDS.idUserFriend -- get my freinds friends
      AND UF2.idUserFriend NOT IN 
           (
              SELECT idUserFriend
              FROM USERFRIENDS UF1
              WHERE UF1.idUser =1           
           )

The advantage with this query is that the first sub query is part of join and will not be executed for every record.

Unfortunately, you will need to use an subquery for NOT IN case.

edited Jun 21 '12 at 11:57

answered Jun 21 '12 at 11:26

Kshitij

8,474
2
26
34

This one gives me all the friends of friends. Even the ones i already have as friends. So this is not what i am looking for. I only have to get the friends of my friends who are not my friends. – Alvaro Jun 21 '12 at 11:41

Performance for select friends of friends sql (but not my friends)

4 Answers4