title: improving a friends list query : counting the mutual friends
body: <p>i have two tables in my database one is to keep users info (users_table ) 
  and the other one keeps track of the friends </p>
  
  <p>users_table:</p>
  
  <pre><code>id    username      avatar  
  1         max       max.jpg  
  2         jack      jack.jpg  
  </code></pre>
  
  <p>friends_table :</p>
  
  <pre><code>id    u1_id      u2_id  
  1         1          2  
  2         1          3  
  </code></pre>
  
  <p>in every user profile i show his/her friends list</p>
  
  <p>here is my query </p>
  
  <pre><code>select u.id,
      u.username,
      u.avatar
  from friends_table f
  join users_table u on f.u1_id = u.id || f.u2_id = u.id
  where u.id &lt;&gt; $profile_id
      and (f.u1_id = $profile_id || f.u2_id = $profile_id)
  </code></pre>
  
  <p>this query selects friends of the profile owner  ($profile_id) </p>
  
  <p>and join them with the user table to get each friend username and avatar</p>
  
  <p>now i want to count the mutual friends between each friend and the profile  owner is it possible to this in one query or should i do some long and probably slow query like this for each founded friend( it's just a example and it might have some syntax error ): </p>
  
  <pre><code>       foreach ( $friends_list_query_resul as $qr ){
         $friend_id = $qr['id'];
  
         $mutual_count = mysql_query
      ( "select count(*) from friends_table where 
      ($u1_id = $friend_id || $u2_id = $friend_id )
                 &amp;&amp; 
  
  
      ( $u1_id IN ( SELECT `u1_id`,`u2_id` from friends_table where
       ($u1_id = $profile_id || $u2_id = $profile_id ) )
  
  ||
  
        $u2_id IN ( SELECT `u1_id`,`u2_id` from friends_table where
       ($u1_id = $profile_id || $u2_id = $profile_id ) )
  
  
         ")
          }
  </code></pre>