title: Query that finds connection (friend, friend of friend) too slow
body: <p>If two users are friends, I have one entry in the "friends" table for them like this:</p>
  
  <pre><code>   table: friends
     ------------------------
     uid1           uid2
     332            333
  </code></pre>
  
  <p>Now let's say a new user (with uid=100) joins. My app finds the users near her, and then tries to show their connection (fb friends, friends of friends, etc.) to her. I use this query for this purpose:</p>
  
  <pre><code>           SELECT (fr.id IS NOT NULL)+(f2.id IS NOT NULL)*2+(fr.id IS NULL AND f2.id IS NULL)*3 AS connection
             FROM users u
             LEFT JOIN friends AS fr ON ((fr.uid1='100' AND fr.uid2=u.uid) OR (fr.uid1=u.uid AND fr.uid2='100'))
             LEFT JOIN (friends AS f1, friends AS f2) ON fr.id IS NULL AND
             (
              (f1.uid1='100' AND f1.uid2=f2.uid1 AND f2.uid2=u.uid) OR
              (f1.uid2='100' AND f1.uid1=f2.uid1 AND f2.uid2=u.uid) OR
              (f1.uid2='100' AND f1.uid1=f2.uid2 AND f2.uid1=u.uid) OR
              (f1.uid1='100' AND f1.uid2=f2.uid2 AND f2.uid1=u.uid)
             ) 
             WHERE [location near her] 
  </code></pre>
  
  <p>This query will find people near the new user, and for each one the value of "connection" will be 1 if friends with the new user, 2 if they have a common friend, and 3 otherwise.</p>
  
  <p>The query used to work okay but now that I have just a little more users (10,000) it has become very slow (takes 10 seconds to find the type of connection for just 10 people near the new user).
  Is there something I'm doing completely wrong in my query or database scheme? I know that defining indices will make things faster but still I think taking 10 seconds for such simple query is very long. I'd appreciate any suggestions.</p>