sql - Suggest users based on common interest - MySql query -

basically trying suggest people based on common interests.

i have table of users(id, username, firstname, lastname, etc)

i have table of interested_people userid + interested_in stored.

i have table of contactlist people added each other stored.(user1, user2, accepted [1,0])

what want select * users table not friend , have same interest me well.

i searched lot in internet couldn't find so.

here have created query , want. slow. takes 16 20 second output in phpmyadmin in local machine. kindly request guys if can edit query bit , make bandwidth & time efficient.  select *    users   id in(select userid                 interested_people                interested_in in(select interested_in                                         interested_people                                        userid = [userid])                      , id not in(select user1 my_friends_userid                                      contactlist f                                     f.user2 = [userid]                                      , accepted = 1                                    union                                    select user2 my_friends_userid                                      contactlist f                                     f.user1 = [userid]                                      , accepted = 1))         , id != [userid]  order  rand ()  limit  0, 10; 

[userid] in query id of user online. if m online id 1.

this query suggest 10 random users not friends , have same interests me. slooow.

thanks in advance!

your problem suggests self-join users common interests. then, not exists avoid contact list. following gets list of users common interests, ordered number of common interests:

select ip2.userid, count(*) numincommon interested_people ipme join      interested_people ip2      on ipme.interested_in = ip2.interested_in ,         ipme.userid = $userid ,  -- user id goes here         ip2.userid <> ipme.userid not exists (select 1                    contactlist cl                   cl.user1 = ipme.userid , cl.user2 = ip2.userid ,                         cl.accepted = 1                  ) ,       not exists (select 1                    contactlist cl                   cl.user1 = ip2.userid , cl.user2 = ipme.userid ,                         cl.accepted = 1                  ) group ip2.userid;