Alex Stetsenko

If table is not empty, there may be duplicated records. If so a regular alter query to create a new unique index will return an error like below.

ERROR 1062 (23000): Duplicate entry ’132653-47′ for key 1

However keyword IGNORE allows to force a new unique index to drop duplicated rows.

ALTER IGNORE TABLE `t` ADD UNIQUE INDEX `i` (`f1`, `f2`);

In this case the output will be something like this:

Query OK, 507 rows affected (0.02 sec)
Records: 507 Duplicates: 0 Warnings: 0

So simply using IGNORE helps to save time on writing a custom script to clean up duplicates.