Find Duplicate Rows in MySQL

-- Which values are duplicated, and how many times
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- The actual duplicate rows, keeping the lowest id as the survivor
SELECT u.*
FROM users u
JOIN (
    SELECT email, MIN(id) AS keep_id
    FROM users
    GROUP BY email
    HAVING COUNT(*) > 1
) d ON d.email = u.email
WHERE u.id <> d.keep_id;

-- Inspect first, then delete. Never run the delete blind.