Speed Up Query to Delete Duplicate Records but Retain One

Asked Mar 09 '23 at 10:56

Active Mar 09 '23 at 12:39

Viewed 48 times

I have a table of c6m records. The id field is the unique primary key but MName, SName and Date/Time are not unique.

I want to delete all duplicate records whilst retaining one. The query below takes several hours to run on a reasonably powerful PC. Is there a quicker/more efficient way of doing this?

Thanks

DELETE t1 FROM r.test t1, r.test t2 
WHERE t1.id > t2.id AND t1.MName = t2.MName AND t1.SName = t2.SName AND t1.`Date/Time` = t2.`Date/Time`

asked Mar 09 '23 at 10:56

Robsmith

2

you can try adding index on **MName, SName** to speed up the query, – DEV Mar 09 '23 at 11:04
1

You really created a column named `Date/Time`? – RiggsFolly Mar 09 '23 at 11:06
1

https://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql – RiggsFolly Mar 09 '23 at 11:11

Speed Up Query to Delete Duplicate Records but Retain One

0 Answers0