将备选方案连接到子查询

Question

我试图向登录用户显示他们每次登录时尚未看到的通知数。为此，我有以下疑问：

"select count(eventId) as count from notifications where updateId in("
 # 2-the updates NOT IN what the user has seen
 "select updateId from updates where updateId not in("
 # 1-the updates the user has seen
 "select updateId from seen_updates where memberId={}))".format(memberId))

问题是，随着通知数量的增加，这似乎需要一段时间。有没有一种方法可以通过连接做得更好？

mysql> describe notifications;
+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| eventId  | int(10) unsigned | NO   |     | NULL    |       |
| updateId | int(10) unsigned | YES  |     | NULL    |       |
+----------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe updates;
+---------------+------------------+------+-----+-------------------+----------------+
| Field         | Type             | Null | Key | Default           | Extra          |
+---------------+------------------+------+-----+-------------------+----------------+
| updated       | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
| updateId      | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| eventCategory | varchar(40)      | YES  |     | NULL              |                |
| eventsCount   | int(10) unsigned | NO   |     | NULL              |                |
+---------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

mysql> describe seen_updates;
+----------+------------------+------+-----+-------------------+-------+
| Field    | Type             | Null | Key | Default           | Extra |
+----------+------------------+------+-----+-------------------+-------+
| updateId | int(10) unsigned | NO   |     | NULL              |       |
| memberId | int(10) unsigned | NO   |     | NULL              |       |
| updated  | timestamp        | NO   |     | CURRENT_TIMESTAMP |       |
+----------+------------

Answer 1

没有必要将查询更改为连接并承担风险，因为根据架构，两个索引后面的详细信息首先是主要缺失的对象，这会导致速度变慢

>

表“seed\u Updates”的“member\u id”列上的索引

Answer 2

通过连接，您可以这样做：

select     count(eventId) as count 
from       notifications 
inner join updates 
        on updates.updateId = notifications.updateId
left join  seen_updates 
        on seen_updates.updateId = notifications.updateId
       and seen_updates.memberId = {}
where      seen_updates.updateId is null

根据您的数据结构，您甚至可能不需要包含内部连接更新的子句。通知似乎是合乎逻辑的。updateId需要在updates表中有一个现有的对应项。仅当不能保证内部联接需要保留时。