New-To-SQL New-To-SQL - 15 days ago 7
MySQL Question

Excluding email lists while selecting specific values at once

I am trying to do 2 things here within 1 SELECT query (I know I could do this in 2 steps but I want to avoid intermediary tables):

1) Select specific values (M and W only -- there are other values possible but I just want these 2 and not other) from a TABLE 'anothertable'.
2) While avoiding selecting opt-outs and bounced emails. (11k rows, <5 columns)

I am not sure if the syntax is correct since the query is loading for ever. Size should not be an issue though (27k rows, <5 columns and 2 core processors).

BEGIN

CREATE TABLE `mytable` AS (

SELECT * FROM `anothertable` A

WHERE NOT EXISTS (SELECT Email FROM `optout` B WHERE B.Email = A.Email)
AND NOT EXISTS (SELECT Email FROM `allbounced` C WHERE C.emailbounced = A.Email)

GROUP BY Email
HAVING
(SUM(CASE WHEN target = 'M' THEN 1 ELSE 0 END) > 0 OR -- MAN is present
SUM(CASE WHEN target = 'W' THEN 1 ELSE 0 END) > 0 AND -- WOMAN is present
SUM(CASE WHEN target NOT IN ('M', 'W') THEN 1 ELSE 0 END) = 0) -- only MAN or WOMAN

);

END

Answer

When possible you should use NOT IN instead of NOT EXISTS. This should be faster:

BEGIN

CREATE TABLE `mytable` AS (

SELECT Email FROM `vbq-sales-customers` A
WHERE Email NOT IN (SELECT Email FROM `vbq-zb-optout`)
AND Email NOT IN (SELECT emailbounced FROM `vbq-allbounced`)

GROUP BY Email
HAVING 
(SUM(CASE WHEN target = 'M' THEN 1 ELSE 0 END) > 0 OR   -- MAN  is present
SUM(CASE WHEN target = 'W' THEN 1 ELSE 0 END) > 0 AND   -- WOMAN  is present
SUM(CASE WHEN target NOT IN ('M', 'W') THEN 1 ELSE 0 END) = 0)  -- only MAN or WOMAN

);

END
Comments