Mysql Select Distinct w TRIM() and Subselect Optimize Performance

Question

I have a table mailing_list with the columns

id, optout, email

The email has duplicates, but the optout isn't the same even if the email is the same. So we can have data like this...

id optout email

1   0     [email protected]
2   1     [email protected]
3   0     [email protected]
4   0     [email protected]
5   1     [email protected]
6   0     [email protected]
7   1     [email protected]
8   0     [email protected]

The end result I'm trying to acheive is two lists, optin and optout

So far the best way I've found to do this is with the following queries, but performance is very slow, so trying to find a better way. The TRIM function needs to be in there because some of the emails have whitspace and some don't so when selecting distinct, I will still get dupes if I don't use TRIM.

(OPTIN)

select distinct TRIM(email) as emails from mailing_list where optout = 0
and email not in (select email from mailing_list where optout = 1) order by emails

(OPTOUT)

select distinct TRIM(email) as emails from mailing_list where optout = 1
and email not in (select email from mailing_list where optout = 0) order by emails

Any help would be much appreciated.

Thanks

Answer 1

For those who have opted out:

select trim(email) as trimmed_email, sum(optout) as qty
from mailing_list
group by trimmed_email
having qty > 0;

For those who have opted in:

select trim(email) as trimmed_email, sum(optout) as qty
from mailing_list
group by trimmed_email
having qty = 0;