Mysql query optimization

Question

HI im running socialengine3 and need optimization on the a custom Mutual friends query. Its currently taking 15 seconds to execute

Friends table

friend_id
friend_user_id1
friend_user_id2
friend_status
friend_type

users

user_id

Edited

I have converted in into exists and still its now executing in 20 seconds.

below is the updated query.

 SELECT friendlist.friend_user_id2, se_users.username, se_users.id, se_users.image, se_users.name, se_users.surname, count( * ) AS mutral_friends
 FROM `se_friends` friendlist
 INNER JOIN `users` se_users ON friendlist.friend_user_id2 = `se_users`.id
 WHERE EXISTS (

    SELECT se.friend_user_id2
    FROM se_friends se
    WHERE se.friend_user_id1 = '105012'
    AND se.friend_status = '1'
    AND se.friend_user_id2 = friendlist.friend_user_id1
 ) AND NOT EXISTS (
    SELECT se1.friend_user_id2
    FROM `se_friends` se1
    WHERE se1.friend_user_id1 = '105012'
    AND friendlist.friend_user_id2 = se1.friend_user_id2
 )
 AND NOT (
    friendlist.friend_user_id2 = '105012'
 )
 AND friendlist.friend_status = '1'
 GROUP BY friendlist.friend_user_id2, se_users.username, se_users.id, se_users.image,         se_users.name, se_users.surname
 ORDER BY mutral_friends DESC
 LIMIT 0 , 20 

Orignal query

SELECT DISTINCT `se_friends`.friend_user_id2, se_users.username, se_users.id, se_users.image, se_users.name, se_users.surname, count(*) as mutral_friends
            FROM `se_friends`
            INNER JOIN `users` se_users` ON `se_friends`.friend_user_id2=`se_users`.id
            WHERE 
              (se_friends.friend_user_id1 <> '30355' or se_friends.friend_user_id2 <> '30355') AND

                se_friends.friend_user_id1 IN 
                (SELECT se_friends.friend_user_id2
                 FROM `se_friends`
                 WHERE se_friends.friend_user_id1='".$user_id."'  AND se_friends.friend_status='1')
            AND `se_friends`.friend_user_id2 NOT IN
                (SELECT se_friends.friend_user_id2
                FROM `se_friends`
                WHERE se_friends.friend_user_id1='".$user_id."'
                )
            AND NOT(se_friends.friend_user_id2='".$user_id."') AND se_friends.friend_status='1' 
            GROUP BY `se_friends`.friend_user_id2, se_users.username, se_users.id, se_users.image, se_users.name, se_users.surname
            ORDER BY  mutral_friends DESC 
            LIMIT 0, 20

Answer 1

IN is very expensive operation. try to replace it with EXISTS. eg

select * from table where user_id in (select user_id from users where active='A')

and

select * from table t where exists (select user_id from users u where t.user_id = u.user_id and u.active='A')

if it won't be helpful, it's better to look at execution plan