加入子查询的替代方案
我试图向登录用户显示他们每次登录时都没有看到的通知数。为此，我有以下查询：
"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 | | +----------+------------
加入你可以这样做：
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
根据您的数据结构，您甚至可能不需要使用inner join updates的子句。 notification.updateId需要在updates表中具有现有的对应部分似乎是合乎逻辑的。 只有当这不能保证inner join需要保留。