为什么这个MYSQL在查询中不那么慢？

I have a query that checks against another table to see if an email is in there and matches other conditions. However it takes extremely long with only 10k rows. Everything is indexed properly. I was under the assumption that the NOT IN sub-query would only run once? Is there a better way to write this?

我有一个查询,检查另一个表,看看是否有电子邮件,并匹配其他条件。然而,只需要10k行就需要很长时间。一切都正确索引。我假设NOT IN子查询只运行一次?有没有更好的方法来写这个?

SELECT
    COUNT(*) AS `numrows`
FROM
    (
        SELECT
            COUNT(end_users.email)
        FROM
            `end_users`
        WHERE
            `email` NOT IN (
                SELECT
                    email
                FROM
                    email_que
                WHERE
                    email_cronjob_id IN (1, 2)
            )
    ) count_results

2 个解决方案

#1

You could rewrite the sql avoiding NOT IN

你可以重写sql避免NOT IN

SELECT
    COUNT(*) AS numrows
FROM
    (
        SELECT
            COUNT(eu.email)
        FROM
            end_users eu
        WHERE
         NOT   EXISTS   (
                SELECT
                    email
                FROM
                    email_que eq
                WHERE
                    eq.email_cronjob_id IN (1,2)  AND eu.email  =  eq.email
            )
    ) count_results

#2

-1

Maybe you could try to use LEFT JOIN instead of NOT IN subquery.

也许你可以尝试使用LEFT JOIN而不是NOT IN子查询。

SELECT
    COUNT(*) AS `numrows`
FROM
(
    SELECT
        COUNT(t1.email) 
    FROM 
        `end_users` AS t1
    LEFT JOIN
        `email_que` AS t2 ON t1.email = t2.email AND t2.email IS NULL
    WHERE 
        t2.email_cronjob_id IN (1,2)
) count_results

#1