let's optimize a query =]
Hope you guys can help me optimize this query. I've got 2 tables: users and user_friends which is a 1:1 reflective join on the users table. (i.e. user_friends records are like [user_id, friend_id], [friend_id, user_id]).

Let's say I have a user called "self". What I'm trying to do is find all users who have a mutual friend in common with "self" and who meet some other simple criteria in the users table. This is the best I came up with so far, which runs in about 18 seconds. Can anyone do better?

    SELECT 
      DISTINCT(friend_id) 
    FROM 
      user_friends, users 
    WHERE 
      users.id != #{self.id}
    AND (
      signed_up IS NOT NULL 
      OR 
      user_id IN (
        SELECT 
          friend_id 
        FROM 
          user_friends 
        WHERE 
          user_id = #{self.id}
      )
    ) 
    AND 
      users.id = friend_id
    AND 
      relationship_status = 'Single'
    AND 
      current_location_id IS NOT NULL
    ;

Also, here is the EXPLAIN for the above query. Maybe the "temporary" table is causing the problem?

    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: user_friends
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 16
              ref: NULL
             rows: 1316316
            Extra: Using where; Using index; Using temporary
    *************************** 2. row ***************************
               id: 1
      select_type: PRIMARY
            table: users
             type: eq_ref
    possible_keys: PRIMARY,fk_users_current_location_id
              key: PRIMARY
          key_len: 8
              ref: yoke_int.user_friends.friend_id
             rows: 1
            Extra: Using where; Distinct
    *************************** 3. row ***************************
               id: 2
      select_type: DEPENDENT SUBQUERY
            table: user_friends
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 16
              ref: const,func
             rows: 1
            Extra: Using index

let's optimize a query =]
===

Hope you guys can help me optimize this query. I've got 2 tables: users and user_friends which is a 1:1 reflective join on the users table. (i.e. user_friends records are like [user_id, friend_id], [friend_id, user_id]).

Let's say I have a user called "self". What I'm trying to do is find all users who have a mutual friend in common with "self" and who meet some other simple criteria in the users table. This is the best I came up with so far, which runs in about 18 seconds. Can anyone do better?

    SELECT 
      DISTINCT(friend_id) 
    FROM 
      user_friends, users 
    WHERE 
      users.id != #{self.id}
    AND (
      signed_up IS NOT NULL 
      OR 
      user_id IN (
        SELECT 
          friend_id 
        FROM 
          user_friends 
        WHERE 
          user_id = #{self.id}
      )
    ) 
    AND 
      users.id = friend_id
    AND 
      relationship_status = 'Single'
    AND 
      current_location_id IS NOT NULL
    ;

Also, here is the EXPLAIN for the above query. Maybe the "temporary" table is causing the problem?

    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: user_friends
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 16
              ref: NULL
             rows: 1316316
            Extra: Using where; Using index; Using temporary
    *************************** 2. row ***************************
               id: 1
      select_type: PRIMARY
            table: users
             type: eq_ref
    possible_keys: PRIMARY,fk_users_current_location_id
              key: PRIMARY
          key_len: 8
              ref: yoke_int.user_friends.friend_id
             rows: 1
            Extra: Using where; Distinct
    *************************** 3. row ***************************
               id: 2
      select_type: DEPENDENT SUBQUERY
            table: user_friends
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 16
              ref: const,func
             rows: 1
            Extra: Using index
