4 changed tablename
source | link

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;email_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE email_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

3 added 1 characters in body
source | link

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) >1> 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) >1
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) > 0
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

2 added one-liner and explanation of long method
source | link

I wasn't able testTry this out without knowing your DDL and indexes:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) >1
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250, but try000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

I wasn't able test this out without knowing your DDL and indexes, but try this:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

Try this:

DELETE FROM emailTable WHERE NOT EXISTS (
 SELECT * FROM (
    SELECT MIN(id) minID FROM emailTable    
    GROUP BY email HAVING COUNT(*) >1
  ) AS q
  WHERE minID=id
)

The above worked for my test of 50 emails (5 different emails duplicated 10 times).

You might need to add an index on the 'email' column:

ALTER TABLE emailTable ADD INDEX ind_email (email);

It might be a bit slow fro 250,000 rows. It was slow for me on a table that had 1.5million rows (properly indexed), which is how I came up with this strategy:

/* CREATE MEMORY TABLE TO HOUSE IDs of the MIN */
CREATE TABLE email_min (minID INT, PRIMARY KEY(minID)) ENGINE=Memory;

/* INSERT THE MINIMUM IDs */
INSERT INTO email_min SELECT id FROM email
    GROUP BY email HAVING MIN(id);

/* MAKE SURE YOU HAVE RIGHT INFO */
SELECT * FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* DELETE FROM EMAIL */
DELETE FROM email 
 WHERE NOT EXISTS (SELECT * FROM email_min WHERE minID=id)

/* IF ALL IS WELL, DROP MEMORY TABLE */
DROP TABLE track_table_min;

The benefit of the memory table is there's an index that is used (primary key on minID) that speeds up the process over a normal temporary table.

1
source | link