# Fastest Way to Remove Duplicates

**URL:** <https://forums.percona.com/t/fastest-way-to-remove-duplicates/338>\
**Category:** Other MySQL® Questions\
**Created:** [May 30, 2007, 5:31pm UTC](https://forums.percona.com/t/fastest-way-to-remove-duplicates/338 "2007-05-30T17:31:01Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![GeoffreyF67](https://avatars.discourse-cdn.com/v4/letter/g/bbce88/32.png) [@GeoffreyF67](https://forums.percona.com/u/GeoffreyF67)\
**Post date:** [May 30, 2007, 5:31pm UTC](https://forums.percona.com/t/fastest-way-to-remove-duplicates/338/1 "2007-05-30T17:31:01Z")

</div>

Suppose you had a table that had two columns:

ID (integer)  
Sentence (varchar 255)

Due to legacy reasons, the Sentence field had duplicates in it.

What would be the quickest method to get a UNIQUE on the Sentence column as well as remove the duplicates?

The only way I’ve come up with so far is to rebuild the table from scratch with unique on. Since there are over 25 million rows, that’s gonna take a LONG time )

Any bright ideas?

---

<div class="post-metadata">

**Author:** ![Speeple](https://avatars.discourse-cdn.com/v4/letter/s/73ab20/32.png) [@Speeple](https://forums.percona.com/u/Speeple)\
**Post date:** [June 1, 2007, 4:02am UTC](https://forums.percona.com/t/fastest-way-to-remove-duplicates/338/2 "2007-06-01T04:02:53Z")

</div>

Here’s what I’d do:

Create a new column, Sentence\_checksum BINARY(16), with a regular INDEX.

A binary MD5 checksum, 16 bytes in length rather than HEX alternative of 32… You could always put an index on the Sentence column, but to it would have to be full column length to correct match dupes - meaning a large index.

Then I would perform:

UPDATE table SET Sentence\_checksum=UNHEX(MD5(Sentence))

Then I would create a table exactly like the old table, but this time Sentence\_checksum would be altered to a UNIQUE index type.

I would then perform:

INSERT INTO table\_new SELECT \* FROM table\_old GROUP BY Sentence\_checksum

Then DROP the old table, and rename the new one.

---

<div class="post-metadata">

**Author:** ![GeoffreyF67](https://avatars.discourse-cdn.com/v4/letter/g/bbce88/32.png) [@GeoffreyF67](https://forums.percona.com/u/GeoffreyF67)\
**Post date:** [June 1, 2007, 5:23pm UTC](https://forums.percona.com/t/fastest-way-to-remove-duplicates/338/3 "2007-06-01T17:23:37Z")

</div>

That’s a great idea!

Thanks!