mysql - 我需要更快的 SQL 查询来查找过期的订阅者

Question

我在 MySQL 成员和订阅者中有 2 个表。我想做的是；我想找到至少有 1 个过期订阅且没有有效订阅的订阅者。

SELECT mem.id as id 

FROM members as mem 
INNER JOIN subscriptions as sub ON sub.member_id = mem.id 

WHERE 
sub.active = 0 AND 
NOT EXISTS ( SELECT 1 FROM subscriptions as sub2 WHERE sub2.member_id = mem.id AND sub2.active = 1 )

Group By mem.id

这个查询处理时间太长(考虑到2个表的记录量，这是正常的)。

我等了大约 2 分钟才看到结果，但由于它仍在尝试加载，我只是取消了它。我需要更快的结果。还有其他方法吗？

感谢您的时间和关心。

Answer 1

给你。 (确保你有适当的索引)。另请注意，我留在原始连接中是因为我假设您在某些时候想要的不仅仅是 member_id。但是，如果您只需要 member_id，则可以一起删除 members 表。

/*
insert members (member_name) values ('tom')
insert members (member_name) values ('bob')
insert members (member_name) values ('jim')

declare @tom int
set @tom = (select member_id from members where member_name = 'tom')

insert subscriptions (member_id, is_active) values (@tom, 1)
insert subscriptions (member_id, is_active) values (@tom, 0)

declare @bob int
set @bob = (select member_id from members where member_name = 'bob')

insert subscriptions (member_id, is_active) values (@bob, 0)
insert subscriptions (member_id, is_active) values (@bob, 0)
*/

SELECT m.member_id
FROM members as m 
INNER JOIN subscriptions as s ON s.member_id = m.member_id 
LEFT JOIN subscriptions s2 on s2.member_id = m.member_id and s2.is_active = 1
WHERE 
s.is_active = 0 and
s2.subscription_id is null

Group By m.member_id

或

SELECT s.member_id
FROM subscriptions as s
LEFT JOIN subscriptions s2 on s2.member_id = s.member_id and s2.is_active = 1
WHERE 
s.is_active = 0 and
s2.subscription_id is null

Group By s.member_id