mysql 关注和转推功能

Question

这是一个有点挑战性但很有趣的问题。考虑拥有这些表

推文

tweet_id | retweet_of_id | user_id

关注

user_id | followed_user_id

因此我们将每个“转发为单独 推文”存储为指向原始推文的 ID (retweet_of_id)。这是因为我想在每一个下面单独发表评论。如果某些内容不是转推，则 retweet_of_id 将为 0。

如何使用 MySQL 高效检索以下内容？

• 我自己的推文
• 所有原始推文(来 self 关注的用户)
• 以及推文(来 self 不关注的用户)的第一次转发(由我关注的用户)

而且结果应该是两者的组合(按顺序)，就像 Twitter 的做法一样。
请考虑可能有 1,000,000 条推文，我们只需要最新的推文(例如: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)

所以最后的顺序应该是这些推文:7, 4, 3, 2 (从最近的开始)

Answer 1

我是这样解决的
(这两个都假设推文是按它们的 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(给出了正确的结果，但对于 1,000,000 条推文来说速度太慢了)

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