Monthly Archives: January 2015
This simple mysql command in InnonDB engine may save you 30 minutes if you want to remove the duplicate copies of a row. Consider a table called “your_table_name” having many columns : column1, column2, column3, …. You define a duplicate row as a row having the same values as in column1 and column2. You have just to create a unique index based on those columns.
ALTER IGNORE TABLE your_table_name ADD UNIQUE INDEX give_ur_index_a_name (column1, column2 );
Commit, and check your table again… duplicate rows went away!
Update: In some versions of MySQL, “ALTER IGNORE” won’t ignore the duplicate key problem. So you may have to run the following command :
set session old_alter_table=1;
After adding the index, do not forget to set old_alter_table to “0” again.