Suggest users based on common interest - MySql query

Question

Basically what i am trying to do is to suggest people based on common interests.

I have a table of Users(id, username, firstname, lastname, etc)

I have a table of Interested_People where UserID + Interested_in is stored.

I have a table of Contactlist where people who are added with each other is stored.(user1, user2, accepted [1,0])

What I want is to select * users table who are not my friend and they have same interest with me as well.

I searched a lot in internet but couldn't find something like so.

Here i do have created a query and it does exactly what I want. But it is very slow. Even it takes 16 to 20 second to output in PHPMyAdmin in my local machine. Now I Kindly request you guys if you can edit my query a bit and make it bandwidth & time efficient.

SELECT * 
FROM   users 
WHERE  id IN(SELECT userid 
             FROM   interested_people 
             WHERE  interested_in IN(SELECT interested_in 
                                     FROM   interested_people 
                                     WHERE  userid = [userid]) 
                    AND id NOT IN(SELECT user1 AS my_friends_userid 
                                  FROM   contactlist f 
                                  WHERE  f.user2 = [userid] 
                                    AND accepted = 1 
                                  UNION 
                                  SELECT user2 AS my_friends_userid 
                                  FROM   contactlist f 
                                  WHERE  f.user1 = [userid] 
                                    AND accepted = 1)) 
       AND id != [userid] 
ORDER  BY Rand () 
LIMIT  0, 10;

[Userid] in this query is the ID of the user who is online. Like if i m online my ID will be 1.

This query suggest 10 random users who are not my friends and have same interests as me. But very slooow.

Thanks in advance!

Answer 1

Your problem suggests a self-join to get the users with common interests. Then, not exists to avoid the contact list. The following gets the list of users with common interests, ordered by the number of common interests:

select ip2.userid, count(*) as numInCommon
from interested_People ipme join
     interested_People ip2
     on ipme.interested_in = ip2.interested_in and
        ipme.userid = $UserId and  -- Your user id goes here
        ip2.userid <> ipme.userid
where not exists (select 1 
                  from contactlist cl
                  where cl.user1 = ipme.userid and cl.user2 = ip2.userid and
                        cl.accepted = 1
                 ) and
      not exists (select 1 
                  from contactlist cl
                  where cl.user1 = ip2.userid and cl.user2 = ipme.userid and
                        cl.accepted = 1
                 )
group by ip2.userid;