Dhay Dhay - 3 months ago 10
MySQL Question

What is the best query other than ADD UNIQUE or SELECT GROUP BY to remove duplicates

What is the best query other than

ADD UNIQUE
or
SELECT GROUP BY
to remove duplicates in the large table of 100 million records?

In the contact details table (InnoDB) that I have contains 100 million records with 15 columns. It took 12 minutes to run the below query to group 1 record:

SELECT * FROM table
WHERE field1='aaa' AND field2='bbb' AND field3='ccc' AND field4='ddd' AND field5='eee'
GROUP BY field1, field2, field3, field4, field5


And 0.275ms for the below query with 5000 samples from the table:

ALTER IGNORE TABLE table
ADD UNIQUE(field1(255),field2(255),field3(255),field4(255),field5(255))

Answer

The best for removing duplicate withou group by or unique clause is distinct

 SELECT distinct   field1, field2, field3, field4, field5 FROM table 
 WHERE field1='aaa' 
 AND field2='bbb' 
 AND field3='ccc' 
 AND field4='ddd'
 AND field5='eee' 

(you should avoid select * in this case)