MySQL JOIN 查询 - 消息系统

Question

我有一个消息系统的下表，我想知道如何查询数据库以了解有多少对话有新消息。

我的表如下

Conversation
------------
id
subject

Messages
--------
id
conversation_id
user_id (sender)
message
timestamp (time sent)

Participants
------------
conversation_id
user_id
last_read (time stamp of last view user viewed conversation)

我正在尝试执行以下查询，但它没有返回结果：

SELECT COUNT(m.conversation_id) AS count
FROM (messages_message m)
INNER JOIN messages_participants p ON p.conversation_id = m.conversation_id
WHERE `m`.`timestamp` > 'p.last_read'
AND `p`.`user_id` = '5'
GROUP BY m.conversation_id
LIMIT 1 

另外，我可能必须在每个页面加载时运行此查询 - 有任何使其尽可能快的提示吗？

干杯

编辑

我还有另一个有点相关的问题，如果有人愿意帮忙的话。

我正在尝试检索主题、对话中的最后一条消息、上次对话的时间戳和新消息的数量。我相信我有一个有效的查询，但看起来组合起来有点糟糕。我可以对此做哪些改进？

SELECT SQL_CALC_FOUND_ROWS c.*, last_msg.*, new_msgs.count as new_msgs_count 
                        FROM ( messages_conversation c ) 
                        INNER JOIN messages_participants p ON p.user_id = '5' 
                        INNER JOIN ( SELECT m.* 
                                    FROM (messages_message m) 
                                    ORDER BY m.timestamp DESC 
                                    LIMIT 1) last_msg 
                                ON c.id = last_msg.conversation_id 
                        LEFT JOIN ( SELECT COUNT(m.id) AS count, m.conversation_id, m.timestamp  
                                    FROM (messages_message m) ) new_msgs 
                                ON c.id = new_msgs.conversation_id AND new_msgs.timestamp > p.last_read 
                        LIMIT 0,10

我应该通过在 MySQL 中执行 IF 语句来确定对话是否未读，还是应该在 PHP 上转换和比较时间戳？

再次感谢， RS7

Answer 1

上面引用的 'p.last_read' 是一个字符串常量 - 删除其中的引号，看看是否会改变任何东西，RS7。如果 user_id 是整数，则还要删除 '5' 中的引号。

就性能而言，请确保所有相关列都有索引。 messages_participants.user_id 和 messages_message.timestamp 是要索引的两个重要列。

Answer 2

是的，您的查询有问题。

首先，您应该注意到您对正在分组的列进行了计数，因此计数结果将为 1。
其次，您将时间戳与字符串进行比较： m.timestamp > 'p.last_read'。
最后，当您知道查询将返回一行时，请避免使用 LIMIT（自信点：p）。

尝试：

SELECT
  COUNT(m.conversation_id) AS count
FROM
  messages_message m
INNER JOIN
  messages_participants p ON p.conversation_id = m.conversation_id
WHERE
  m.timestamp > p.last_read
  AND p.user_id = 5

如果您想增加查询运行时间，您可以在 message_participants (conversation_id, user_id) 中创建一个新索引来索引每个用户的对话，然后更改您的查询：

SELECT
  COUNT(m.conversation_id) AS count
FROM
  messages_message m
INNER JOIN
  messages_participants p ON p.conversation_id = m.conversation_id AND p.user_id = 5
WHERE
  m.timestamp > p.last_read

以便您的数据库引擎现在可以通过简单地查找来过滤 JOIN在索引表中。您也可以通过对时间戳进行索引来更深入地思考这一点：(timestamp,conversation_id,user_id)并将where条件放入连接条件中。

无论您选择什么，始终将最具选择性的领域放在第一位，以提高选择性。

编辑

首先，让我们评论您的查询：

SELECT
  SQL_CALC_FOUND_ROWS c.*,
  last_msg.*,
  new_msgs.count as new_msgs_count
FROM
  messages_conversation c
INNER JOIN
  messages_participants p ON p.user_id = 5 -- Join with every conversations of user 5; if id is an integer, avoid writing '5' (string converted to an integer).
INNER JOIN
( -- Select every message : you could already select here messages from user 5
  SELECT
    * 
  FROM
    messages_message m
  ORDER BY -- this is not the goal of ORDER BY. Use MAX to obtain to latest timestamp.
    m.timestamp DESC 
  LIMIT 1
) last_msg ON c.id = last_msg.conversation_id  -- this query return one row and you want to have the latest timestamp for each conversation.
LEFT JOIN
(
  SELECT
    COUNT(m.id) AS count,
    m.conversation_id,
    m.timestamp  
  FROM
    messages_message m
) new_msgs ON c.id = new_msgs.conversation_id AND new_msgs.timestamp > p.last_read 
LIMIT 0,10

让我们重新表述您的查询：
选择对话主题的新消息数、最后一条消息以及用户@id 的时间戳。

逐步执行：

为每个用户选择最后一条消息、对话中的时间戳：

SELECT -- select the latest timestamp with its message
  max(timestamp),
  message
FROM
  messages_message
GROUP BY
  user_id

聚合函数（MAX、MIN、SUM，...）适用于当前组。读起来就像“对于每个组，计算聚合函数，然后在条件成立的情况下选择我需要的”。因此，这将导致每组一行。
因此，最后一个查询选择 messages_message 表中每个用户的最后一条消息和时间戳。正如您所看到的，通过添加 WHERE 子句，可以轻松为特定用户选择此值：

SELECT
  MAX(timestamp),
  message
FROM
  messages_message
WHERE
  user_id = @id
GROUP BY
  user_id

每个对话的消息数：对于每个对话，计算消息数

SELECT
  COUNT(m.id) -- assuming id column is unique, otherwise count distinct value.
FROM
  messages_conversation c
INNER JOIN -- The current user participated to the conversation
  messages_participant p ON p.conversation_id = c.id AND p.user_id = @id
OUTER JOIN -- Messages of the conversation where the current user participated, newer than last read its time
  messages_message m ON m.conversation_id = c.id AND m.timestamp > p.last_read = @id
GROUP BY
  c.id -- for each conversation

INNER JOIN 不会返回行用于当前用户未参与的对话。
然后，如果条件为 false，OUTER JOIN 将与 NULL 列连接，以便 COUNT 将返回 0 - 没有新消息。

把它们放在一起。

选择当前用户参与的对话中的最后一条消息和时间戳以及每个对话中的新消息数。
这是最后两个查询之间的 JOIN。

SELECT
   last_msg.conversation_id,
   last_msg.message,
   last_msg.max_timestamp,
   new_msgs.nb
FROM
(
  SELECT
    MAX(timestamp)   AS max_timestamp,
    message,
    conversation_id
  FROM
    messages_message
  WHERE
    user_id = @id
  GROUP BY
    user_id
) last_msg
JOIN
(
  SELECT
    c.id         AS conversation_id
    COUNT(m.id)  AS nb
  FROM
    messages_conversation c
  INNER JOIN
    messages_participant p ON p.conversation_id = c.id AND p.user_id = @id
  OUTER JOIN
    messages_message m ON m.conversation_id = c.id AND m.timestamp > p.last_read = @id
  GROUP BY
    C.id
) new_msgs ON new_msgs.conversation_id = last_msg.conversation_id
-- put here and only here a order by if necessary :)