Get all users with duplicate email addresses

Question

I am writing a MySQL query where I can get all records from the user table with duplicate email addresses.

This is the query I have so far.. my PHPMyAdmin keeps loading:

select personal_email, userid
from user
where personal_email in (
select personal_email
from user
group by personal_email
having count(personal_email) > 1);

If I use the query below I get all double emails just once:

select personal_email, count(personal_email)
from user
group by personal_email
having count(personal_email) > 1

The goal is getting all the records with duplicate emails.

Answer 1

Try the query with a JOIN instead of IN:

SELECT  user.personal_email, user.userid
FROM    user 
        INNER JOIN
        (   SELECT  personal_email
            FROM    User
            GROUP BY personal_email
            HAVING COUNT(*) > 1
        ) dupe
            ON dupe.personal_email = user.personal_email;

MySQL often optimises INNER JOINs much better.

Answer 2

Perhaps the most efficient way to do this in MySQL is to use an exists clause:

select u.*
from user u
where exists (select 1
              from user u2
              where u.personal_email = u2.personal_email and
                    u.userid <> u2.userid
             );

For optimal performance, create an index on user(personal_email, userid).