MySQL remove duplicates from big database quick

by

I believe this will do it, using on duplicate key + ifnull():

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
    on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

Should be much faster than anything that requires group by or distinct or a subquery, or even order by. This doesn’t even require a filesort, which is going to kill performance on a large temporary table. Will still require a full scan over the original table, but there’s no avoiding that.

More Related Contents:

  1. MySQL ON DUPLICATE KEY UPDATE for multiple rows insert in single query
  2. How to delete duplicate rows in SQL Server?
  3. MySQL Insert query doesn’t work with WHERE clause
  4. Delete from two tables in one query
  5. PostgreSQL GROUP BY different from MySQL?
  6. GROUP BY behavior when no aggregate functions are present in the SELECT clause
  7. Fulltext Search with InnoDB
  8. MySQL’s now() +1 day
  9. How to search multiple columns in MySQL?
  10. Finding duplicate rows in SQL Server
  11. In SQL / MySQL, what is the difference between “ON” and “WHERE” in a join statement?
  12. What does DELIMITER // do in a Trigger?
  13. Does Mysql have an equivalent to @@ROWCOUNT like in mssql?
  14. MySQL equivalent of DECODE function in Oracle
  15. How do you version your database schema? [closed]
  16. MySQL, Get users rank
  17. Mysql: how to select groups having certain values?
  18. How to fill date gaps in MySQL?
  19. Boolean vs tinyint(1) for boolean values in MySQL
  20. MySQL: “The SELECT would examine more than MAX_JOIN_SIZE rows”
  21. Function for week of the month in mysql
  22. MySQL’s alternative to T-SQL’s WITH TIES
  23. Find rows that have the same value on a column in MySQL
  24. what is the difference between GROUP BY and ORDER BY in sql
  25. What does collation mean?
  26. What’s the correct name for an “association table” (a many-to-many relationship) [closed]
  27. MySQL: Count records from one table and then update another
  28. sql pulling a row for next or previous row of a current row
  29. Row Rank in a MySQL View
  30. Select Rows with Maximum Column Value group by Another Column

Leave a Comment