Deleting duplicates from a large table

Question

Hi,

I have quite a large table with 19 000 000 records, and I have problem with duplicate rows. There's a lot of similar questions even here in SO, but none of them seems to give me a satisfactory answer. Some points to consider:

• Row uniqueness is determined by two columns, location_id and datetime.
• I'd like to keep the execution time as fast as possible (< 1 hour).
• Copying tables is not very feasible as the table is several gigabytes in size.
• No need to worry about relations.

As said, every location_id can have only one distinct datetime, and I would like to remove all the duplicate instances. It does not matter which one of them survives, as the data is identical.

Any ideas?

Answer 1

SELECT *, COUNT(*) AS Count
FROM table
GROUP BY location_id, datetime
HAVING Count > 2

Answer 2

UPDATE table SET datetime  = null 
WHERE location_id IN (
SELECT location_id 
FROM table as tableBis
WHERE tableBis.location_id = table.location_id
AND table.datetime > tableBis.datetime)

SELECT * INTO tableCopyWithNoDuplicate FROM table WHERE datetime is not null

DROp TABLE table 

RENAME tableCopyWithNoDuplicate to table

So you keep the line with the lower datetime. I'm not sure about perf, it depends on your table column, your server etc...

Answer 3

I think you can use this query to delete the duplicate records from the table

ALTER IGNORE TABLE table_name ADD UNIQUE (location_id, datetime)

Before doing this, just test with some sample data first..and then Try this....