**Can You Remove Duplicate Records by Modifying an Existing Index to Unique or Adding a New One?**

Removing Duplicates with Unique Index

In the quest to prevent duplicate records, a common approach is to create a unique index on the relevant table columns. However, a recent mistake resulted in the creation of a normal index instead, allowing duplicates to persist within a massive 20 million record table.

Question: Will modifying the existing index to unique or adding a new unique index for those columns remove the duplicate records, or will the operation fail due to existing unique entries?

Answer:

Adding a unique index with the IGNORE modifier will remove duplicate records:

<code class="sql">ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);</code>

However, this modifier is deprecated in MySQL 5.7.4 and later, resulting in an error. To circumvent this issue, the following steps can be taken:

1. Create a temporary table to hold the data from the original table:

   <code class="sql">CREATE TABLE tmp_data SELECT * FROM mytable;</code>

2. Truncate the original table:

   <code class="sql">TRUNCATE TABLE mytable;</code>

3. Create the unique index on the original table:

   <code class="sql">ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);</code>

4. Copy the data back into the original table while ignoring duplicate rows:

   <code class="sql">INSERT IGNORE INTO mytable SELECT * from tmp_data;</code>

5. Drop the temporary table:

   <code class="sql">DROP TABLE tmp_data;</code>

Alternatively, adding a unique index without the IGNORE modifier would cause the query to fail with Error 1062 (duplicate key).

The above is the detailed content of **Can You Remove Duplicate Records by Modifying an Existing Index to Unique or Adding a New One?**. For more information, please follow other related articles on the PHP Chinese website!