Changing MySQL query from username to email causes timeout

Question

If I run this code checking for userid, I get results in 1 second

    SELECT userid, username, email
    FROM db_main.users
    WHERE userid NOT IN 
    (
        SELECT userid 
        FROM db_backup.user 
    );

If I change it to username, I still get results in 1 second

    SELECT userid, username, email
    FROM db_main.users
    WHERE username NOT IN 
    (
        SELECT username 
        FROM db_backup.user 
    );

However, if I change it to email, then the query timeouts after 30 seconds.

    SELECT userid, username, email
    FROM db_main.users
    WHERE email NOT IN 
    (
        SELECT email 
        FROM db_backup.user 
    );

Any ideas why? Thanks!

Do you have any indices setup on the `db_backup_.user` table? — Tim Biegeleisen, Jan 21 '21 at 02:24
Does this answer your question? [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) — Charlieface, Jan 21 '21 at 03:19

score 0 · Answer 1 · answered Jan 21 '21 at 02:50

First of all, don't use NOT IN. you can use NOT EXISTS instead.

SELECT userid, username, email
    FROM db_main.users u
    WHERE NOT EXISTS
    (
        SELECT email 
        FROM db_backup.user b
       WHERE b.email = u.email
    );

Note: search for how to create index and create index on email in both user tables. Also search why should I not use NOT IN. it will lead to wrong result when it comes to NULL.

Changing MySQL query from username to email causes timeout

1 Answers1