CODEWITHSUNDEEP
mysqljoin
Revenant
Revenant

Reputation: 2984

I need faster SQL query to find expired subscribers

I have 2 tables in MySQL members and subscribers. What I want to do is; I would like to find subscribers who has at least 1 expired subscription and no active subscription.

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

This query takes too long to process (considering the amount of the records 2 tables holds, it is normal).

I waited around 2 minutes to see the results but since it was still trying to load, I just cancelled it. I need results faster. Is there any other way to do this?

Thank you for your time and concern.

Upvotes: 1

Views: 1144

Answers (3)

smnsvane
smnsvane

Reputation: 167

DISTINCT and group by should perform the same, but i find the query more readable with DISTINCT.

An index on active will probably speed the query up.

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

Upvotes: 0

tawfekov
tawfekov

Reputation: 5122

I can't guess why you are doing your sql like this but this might work 

SELECT mem.id as id 
FROM members as mem 
INNER JOIN subscriptions as sub ON sub.member_id = mem.id 
WHERE sub.active = 0 
Group By mem.id

Update : I guess you might add deactivted_date column or something to indicate expiration

Upvotes: 1

Chris Gessler
Chris Gessler

Reputation: 23123

Here you go. (make sure you have proper indexes). Also note that I left in the original join because I'm assuming you're going to want more than just member_id at some point. But if all you want is member_id, you could remove the members table all together.

/*
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

OR

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

Upvotes: 1

Related Questions