Mysql: Find and remove duplicates

Find and remove duplicates.

CREATE TABLE mytable(
  rowid INT(11) NOT NULL,
  msgid INT(11) DEFAULT NULL,
  userid INT(11) DEFAULT NULL,
  PRIMARY KEY (rowid)
);
INSERT INTO mytable VALUES 
  (1, 5, 33),
  (2, 5, 12),
  (3, 4, 21),
  (4, 5, 33),
  (5, 5, 33),
  (6, 4, 15),
  (7, 4, 21);

Select duplicates:

SELECT t1.* FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     4 |     5 |     33 |
|     5 |     5 |     33 |
|     7 |     4 |     21 |
+-------+-------+--------+

Remove duplicates with a DELETE statement:

DELETE t1 FROM mytable t1
  JOIN (
        SELECT msgid, userid, MIN(rowid) min_rowid FROM mytable
          GROUP BY msgid, userid
        ) t2
  ON t1.rowid <> t2.min_rowid AND t1.msgid = t2.msgid AND t1.userid = t2.userid;

Remove duplicates with a new unique key:

ALTER IGNORE TABLE mytable
  ADD UNIQUE KEY(msgid, userid);

This ALTER TABLE with IGNORE keyword will create new unique key and remove all duplicates from the table in a one step.

SELECT * FROM mytable;
+-------+-------+--------+
| rowid | msgid | userid |
+-------+-------+--------+
|     1 |     5 |     33 |
|     2 |     5 |     12 |
|     3 |     4 |     21 |
|     6 |     4 |     15 |