A while back i wrote a tutorial on how to remove duplicate rows in a mysql database. That solution worked fine BUT it was ametuarish :)

Here’s a better one without a temporary table using the power of self joins.

DELETE u1 FROM users u1, users u2 WHERE u1.id < u2.id AND u1.email = u2.email

This will delete all the rows from users and leave the one with the lowest id (The first one).

To check if there are still duplicates use this query:

SELECT count(*) as Count, email FROM users u group by email having Count > 1

Should return no rows.

FYI. It’s usually a good idea to add a unique key on email 98% of the time.

ALTER TABLE users ADD UNIQUE email ( email )