I have a huge table that has 1,990,546 users with numerous data. Sometimes I get email lists and dump them into this table also.
I want to delete users with duplicate email addresses. However, I want to keep users WHERE chips > 0
.
This query ran for over an hour before I terminated it:
SELECT id, email
FROM users
WHERE id IN
(
SELECT a.id
FROM users a, users b
WHERE (a.email = b.email)
AND (a.chips > 0)
)
LIMIT 100;
Is there a faster or better way to do this?
Best Answer
I would use a temp table
I have a very good reason for using temp tables as opposed to doing a Direct DELETE JOIN of a table against itself: Performing a DELETE using a subquery that references the same table experiencing the DELETE tends to make keys disappear intermittently as the query is being optimized. I wrote about this back on Feb 22, 2011 : Problem with MySQL subquery
CAVEAT
Please test this out with a smaller dataset to make sure my answer indeed performs what you asked. Make sure you make s backup of the
users
table. To coin a phrase from 1960's Mission Impossible: Should any of your data be caught or killed, I will disavow any knowledge of your actions (Start From 1:50 of the YouTube Video).