query optimization

Question

I have a query of the form

SELECT uid1,uid2 FROM friend WHERE uid1 IN (SELECT uid2 FROM friend WHERE uid1='.$user_id.') and uid2 IN (SELECT uid2 FROM friend WHERE uid1='.$user_id.')

The problem now is that the nested query

SELECT uid2 FROM friend WHERE uid1='.$user_id.'

returns a very large number of ids(approx. 5000).

The table structure of the friend table is uid1(int), uid2(int). This table is used to determine whether two users are linked together as friends.

Any workaround? Can I write the query in a different way? Or is there some other way to solve this issue. I'm sure I am not the first person to face such a problem.

Any help would be greatly appreciated.

Answer 1

You can eliminate one subquery by using a single join with an OR statement.

However, to eliminate any double rows you would have to return only DISTINCT rows.

SELECT DISTINCT f1.uid1, f1.uid2 
  FROM friend AS f1 INNER JOIN 
    friend AS f2 ON (f1.uid1 = f2.uid2 OR f1.uid2 = f2.udi) 
  WHERE f2.uid1='.$user_id.'