A few days ago I faced an issue in my Ruby on Rails application. The issue was caused by some duplicate rows in a very large the MySQL table (more than 1,5 million rows). The obvious solution of course is to remove duplicate rows from the table and add a unique constraint on some columns to make sure no duplicate rows will ever appear again.
The question is how to remove duplicate rows? The table schema is like this:
describe table1;
| id | int(11)
| category | varchar(255)
| image_set_id | int(11)
| position | decimal(16,6)
In my case, it was duplicate on the combination (category,
image_set_id). I want to make sure that combined value
will always be unique.
Searching on Google and I quickly found the solution. The query is something like this:
DELETE n1 FROM names n1, names n2 WHERE n1.id > n2.id AND n1.name = n2.name
…[Read more]
