MySQL users table: find rows that have the same email address

I have a users table in MySQL.

id | username | email

I would like to get the IDs of duplicated users based on the email address. So basically find the Ids of users where the email address can be found more than 1 times in the table.

I wrote som开发者_运维技巧ething like this:

SELECT id
FROM users as users
WHERE (
  SELECT count(id)
  FROM users as users2
  WHERE users.email = users2.email
) > 1

It works ok, but very slow, as the query doesn't seem to use the index. Any other ideas? Thanks!

The solution :

select u.id
from users as u
inner join
(    SELECT email
    FROM users
    GROUP BY users.email
    HAVING count(id) > 1
) as u2 on u2.email = u.email