Удаление дубликатов из большой таблицы

У меня довольно большая таблица с 19 000 000 записями, и у меня проблема с повторяющимися строками. Там много подобных вопросов даже здесь, в SO, но ни один из них, кажется, не дает мне удовлетворительного ответа. Некоторые моменты, которые следует учитывать:

Уникальность строк определяется двумя столбцами, location_id и datetime.
Я хотел бы сохранить время выполнения как можно быстрее (< 1 час).
Копирование таблиц не очень возможно, так как размер таблицы составляет несколько гигабайт.
Не нужно беспокоиться об отношениях.

Как сказано, каждый location_id может иметь только один отдельный datetime, и я хотел бы удалить все повторяющиеся экземпляры. Неважно, какой из них выживает, поскольку данные идентичны.

Любые идеи?

Ответ 1

Я думаю, вы можете использовать этот запрос для удаления дубликатов записей из таблицы

ALTER IGNORE TABLE table_name ADD UNIQUE (location_id, datetime)

Прежде чем делать это, сначала проверьте сначала некоторые данные образца... и затем попробуйте это....

Примечание. В версии 5.5 он работает на MyISAM, но не InnoDB.

Ответ 2

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

Ответ 3

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

Таким образом, вы держите линию с более низким значением datetime. Я не уверен в перфомансе, это зависит от столбца таблицы, вашего сервера и т.д.

Ответ 4

Этот запрос отлично работает для каждого случая: проверен для Engine: MyIsam для 2 миллионов строк.

ALTER IGNORE TABLE имя_таблицы ADD UNIQUE (location_id, datetime)

Ответ 5

Вы можете удалить дубликаты, используя следующие шаги: 1- Экспортируйте следующие результаты запроса в файл txt:

select dup_col from table1 group by dup_col having count(dup_col) > 1

2- Добавьте это в первый из указанных выше txt файлов и запустите окончательный запрос:

delete from table1 where dup_col in (.....)

Обратите внимание, что "..." - это содержимое файла txt, созданного на первом шаге.