提出一种替代方法:

我早些时候在工作中也遇到了同样的问题。我浪费了一周的时间试图找到最好的方法来做到这一点。我最终创建了一个联接表,正如您所做的那样,但该表只包含 未读 消息,而不是跟踪 读

因为

1. 现状是“每个人都读过他们所有的信息”。
2. 应尽快获取未读消息(或其计数)。
3. 现状应该是对系统压力最小的状态。

用户 * 消息 行),在更小的应用中很容易导致数千行的“自重”。如果消息的生命周期是不确定的,那么这个问题就被夸大了——你可能会跟踪多年前的消息状态。

如果跟踪相反的情况,您的“未读消息”表只包含少数行,并且它们会随着用户阅读的每条消息而减少。此外,获取未读消息的数量就像“ SELECT COUNT(*) FROM unread WHERE user = foo ".

但是

就像所有事情一样,这是一种权衡。虽然阅读在计算上尽可能快,但写作是一件苦差事。对于每条书面消息,您需要在此联接表中插入一个条目。此外,如果多人可以阅读同一封邮件,则需要为每个收件人插入一行。如果收件人是隐式的(例如,只给出了用户组的名称,甚至带有“任何有权访问此内容的人”等条件),创建新消息将变得更加复杂。

SELECT
  m.id, m.title, m.sentAt
FROM
  message m
  LEFT JOIN [Read Messages] rm
    ON m.message_id = rm.message_id AND rm.user_id = @userID
WHERE
  rm.user_id IS NULL

如果你有正确的索引,这应该会快得多。

您正在获取该用户的所有消息,并左键单击已读消息。然后,在WHERE子句中,您要求该消息的user_id为NULL,这意味着用户尚未阅读它。

至少在MS SQL上,它会给出一个稍微便宜的查询计划,因为它不需要最后一个过滤(user_id为NULL)

SELECT  ...
FROM    message m
WHERE   NOT EXISTS (
    SELECT 1
    FROM    read_messages rm
    WHERE   rm.usr_id = ...
    AND rm.msg_id = m.msg_id
)