MySQL如何优化一个NOT IN查询？

Question

我在 mysql 上有以下查询，查询逻辑是正确的，但问题是因为有超过 10,000 封搜索者电子邮件和 24,000 多封 guest 电子邮件需要很长时间才能执行，是否有更好的方法来执行此操作？

SELECT g.email, g.name
FROM   guest g
WHERE  g.type='guest' 
AND g.email NOT IN (SELECT email FROM seeker GROUP BY email)
GROUP  BY g.email

Answer 1

试试这个:

SELECT
    g.email, g.name
FROM
    guest g
LEFT JOIN
    seeker s
ON
    s.email = g.email
WHERE
    g.type = 'guest'
AND
    s.email IS NULL
GROUP BY
    g.email;

http://sqlfiddle.com/#!2/d94bf/5