mysql - 数据透视表，在两行之间找到通用属性

我有以下透视表chat_user将用户对象连接到聊天对象。

其行为是，可能存在任意数量的聊天，其中有2个或更多的参与者。我已经在这里说明了这个案子。我正在尝试制定一个有效的查询，它将作为输入16和29391，并且，如果存在这样的子查询，确切地说这两个参与者参与（在这个例子中是1），请返回它进行进一步的处理。它应该忽略其他用户也参与的匹配项，例如chat_id 2。
由于系统的设计方式，此表可能会达到N^2，其中N是用户数，而不考虑组聊天。我需要一个查询，它可以在合理的syncron响应时间内处理这些数字（可能有数百万行），在我写这篇文章时，这听起来越来越不可能了。
所以我正在寻找一个MySQL向导来告诉我这是否合理，甚至可能，或者我是否应该认真重新设计这种安排。我这样做是为了支持1到1个聊天以及组聊天，同时保持完全的引用完整性。我还试图尽可能多地重用代码，因为这个聊天只是大型web应用程序的一个小组件。我真的想避免做1到1和小组聊天作为不同的模式，单独存储和所有，但如果这是疯狂的，那么我没有选择。我以前在sql向导方面有过很好的结果，所以我保持乐观。
另外，请至少进入适度的细节，我是相当新手，当谈到SQL，所以我可能无法拼凑基本指令在一起。
TL；DR：以两个用户id作为输入并返回一个聊天id的查询，前提是且仅当两行正好与给定的用户id匹配且具有某些共同的聊天id时。这对一个可能是百万富翁的行表来说太难了吗？
编辑：这是我目前的工作

SELECT u1.chat_id
FROM   chat_user u1
       INNER JOIN chat_user u2
               ON u1.chat_id = u2.chat_id
WHERE  (u1.user_id = ? AND u2.user_id = ? )
       AND u1.chat_id IN (SELECT chat_id
                          FROM   chat_user
                          GROUP  BY chat_id
                          HAVING Count(chat_id) = 2)

Edit2：：复制粘贴表以使任何对尝试感兴趣的人快速开始

CREATE TABLE `chat_user` (
  `chat_id` int(10) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `engaged` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `chat_user` (`chat_id`, `user_id`, `engaged`) VALUES
(1, 16, 1),
(1, 29391, 1),
(2, 16, 1),
(2, 555, 0),
(2, 29391, 1),
(3, 14, 0),
(3, 29391, 1);

ALTER TABLE `chat_user` ADD PRIMARY KEY (`chat_id`,`user_id`);

最佳答案

以下是我的方法：

SELECT u1.chat_id
FROM chat_user AS u1
JOIN chat_user AS u2
  ON u1.chat_id = u2.chat_id
LEFT JOIN chat_user AS u3
  ON u3.chat_id = u1.chat_id AND u3.user_id NOT IN (u1.user_id, u2.user_id)
WHERE u1.user_id = ? AND u2.user_id = ?
 AND u3.chat_id IS NULL;

您需要(user_id, chat_id)和(chat_id, user_id)上的索引。定义的主键对于后一个索引就足够了。