I've got big (>Mil rows) MySQL database messed up by duplicates. I think it could be from 1/4 to 1/2 of the whole db filled with them.
I need to get rid of them quick (i mean query execution time).
Here's how it looks:
id (index) | text1 | text2 | text3
text1 & text2 combination should be unique,
if there are any duplicates, only one combination with text3 NOT NULL should remain. Example:
1 | abc | def | NULL
2 | abc | def | ghi
3 | abc | def | jkl
4 | aaa | bbb | NULL
5 | aaa | bbb | NULL
...becomes:
1 | abc | def | ghi #(doesn't realy matter id:2 or id:3 survives)
2 | aaa | bbb | NULL #(if there's no NOT NULL text3, NULL will do)
New ids cold be anything, they do not depend on old table ids.
I've tried things like:
CREATE TABLE tmp SELECT text1, text2, text3
FROM my_tbl;
GROUP BY text1, text2;
DROP TABLE my_tbl;
ALTER TABLE tmp RENAME TO my_tbl;
Or SELECT DISTINCT and other variations.
While they work on small databases, query execution time on mine is just huge (never got to the end, actually; > 20 min)
Is there any faster way to do that? Please help me solve this problem.