这是一个有点挑战性但很有趣的问题。考虑使用
推特
tweet_id | retweet_of_id | user_id
跟随
user_id | followed_user_id
因此,我们将每条"转发存储为一条单独的推文",指向原始推文的id(retweet_of_id
)。这是因为我想在每一条下面分别发表评论。如果某个内容不是转发,则retweet_of_id
将是0
。
如何使用MySQL有效地检索以下内容?
- 我自己的推文
- 所有原始推文(来自我关注的用户)
- 以及推特(由我关注的用户转发)的第一次转发(由我不关注的用户)
结果应该是两者的组合(按顺序),就像推特的做法一样。
请考虑可能有1000000条推文,我们只需要最新的推文(例如:10条)。
这里有一个示例(我是用户1,我跟随用户2&3)
tweet_id | retweet_of_id | user_id
----------------------------------
1 0 4 <- EXCLUDE (I don't follow user 4)
2 0 2 <- INCLUDE (I follow user 2)
3 0 3 <- INCLUDE (I follow user 3)
4 1 2 <- INCLUDE (I follow user 2 & first RT)
5 1 3 <- EXCLUDE (I already have the first RT)
6 2 3 <- EXCLUDE (I already have the orignal)
7 0 1 <- INCLUDE (My own tweet)
所以最后的顺序应该是这些tweets:7, 4, 3, 2
(从最近的开始)
以下是我解决它的方法
(这两种方法都假设推文是按其tweet_id
ASC排序的)
解决方案1(正确,运行速度快)
SELECT tweet_id,
FROM tweets
WHERE user = 1 OR user IN (2,3)
GROUP BY IF(retweet_of_id = 0, tweet_id, retweet_of_id)
ORDER BY tweet_id DESC
解决方案2(给出正确的结果,但对于1000000条推文来说速度较慢)
SELECT p1.tweet_id FROM tweets p1
LEFT JOIN tweets p2
ON p2.user IN (2,3)
AND p1.tweet_id > p2.tweet_id
AND (p1.retweet_of_id = p2.tweet_id
OR p1.retweet_of_id AND p1.retweet_of_id = p2.retweet_of_id )
WHERE p2.tweet_id IS NULL
AND (p1.user = 1 OR p1.user IN (2,3))
ORDER BY p1.tweet_id DESC
所有原始推文(来自我关注的用户)
我关注的1个用户:
select user_id from follow where followed_user_id= MyOwnID
2所有原始推文:
select * from tweets where retweed_of_id=0
两者结合:
select * from tweets where retweed_of_id=0 and
user_id in (select user_id from follow where followed_user_id= MyOwnID)
应该是这样——还是我错过了什么?