mysql - 根据共同兴趣推荐用户 - MySql 查询

Question

基本上我想做的是根据共同兴趣推荐人。

我有一个用户表(id、用户名、名字、姓氏等)

我有一个 Interested_People 表，其中存储了 UserID + Interested_in。

我有一个联系人列表表，其中存储了相互添加的人员。(user1，user2，已接受[1,0])

我想要的是选择*用户表中不是我的 friend 但也与我有相同兴趣的用户。

我在互联网上搜索了很多，但找不到类似的东西。

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]是在线用户的ID。就像如果我在线的话我的 ID 就是 1。

此查询建议 10 个随机用户，他们不是我的 friend ，但与我有相同的兴趣。但很慢。

提前致谢!

Answer 1

您的问题建议通过自加入来吸引具有共同兴趣的用户。然后，不存在以避免联系人列表。以下获取具有共同兴趣的用户列表，按照共同兴趣的数量排序:

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;