Поиск дубликатов

Задача достаточно просто решается всего одним запросом вида (опустим уточнение структуры таблицы,условия where могут быть другими):

SELECT * FROM `values` WHERE `columnid` IN (2,3) AND `value` IN ( SELECT `value`  FROM `values` WHERE `columnid` IN (2,3) GROUP BY `value` HAVING COUNT(*)>1 )

где внутренний запрос

SELECT `value`  FROM `values` WHERE `columnid` IN (2,3) GROUP BY `value` HAVING COUNT(*)>1

находит все записи у которых есть повторения по значению поля value, а внешний запрос

SELECT * FROM `values` WHERE `columnid` IN (2,3) AND `value` IN ( [values] )

по полученным значениям определяет все идентификаторы с этими повторяющимися значениями.
Но вот незадача, при количестве записей более 5000 и наличию среди них дубликатов порядка 300 и более, сервер mysql надолго уходит в себя (на локальной машине), или вообще, падает (на среднестатистическом хостинге). Я таким запросом трижды ронял сервер на своем аккаунте (только хостеру не говорите 😉 ). Единственным доступным по скорости решением, которое позволяло обойтись без PHP, а все также выполнить средствами mysql — это сделать в два запроса и одной вспомогательной таблицей.
Суть в том, что мы сохраняем свои результаты вложенного запроса во временной таблице

INSERT INTO `dublicatestmp` (`value`) SELECT `value` FROM `values` WHERE `columnid` IN (2,3) GROUP BY `value` HAVING COUNT(*)>1

Таким образом во втором запросе

SELECT * FROM `values` WHERE `columnid` IN (2,3) AND `value` IN ( SELECT `value`  FROM `dublicatestmp` )

значительно облегчается работа серверу, поскольку исключая необходимость дополнительной фильтрации данных.
Временную таблицу можно создавать динамически с разными префиксами и удалять после получения результата.

Для статистики, в таблице с >6000 записей и 240 повторяющихся значений поиск первых 10ти дубликатов занимал порядка 10-13 сек., при том что поиск самих повторений порядка 0.0003 сек.
При большем количестве повторений, сервер просто падал или зависал на 4-5 минут.
С использованием второго варианта, общее время выполнения 2-х запросов составил менее 3-х секунд.

This entry was posted in MySQL. Bookmark the permalink.

Всего 2 комментариев

  1. Евгений

    Случайно наткнулся на вашу статью по релевантному запросу.
    Ваш способ не сильно быстрый и требует действия через транзакцию по-хорошему.
    Лучше определить поля по которым мы будем определять, является ли страница дубликатом(допустим поля a,b должны быть одинаковы), а далее сделать так:
    ALTER IGNORE TABLE people ADD UNIQUE (a,b);

    Очень быстро работает и в одно действие.

    • Вы наверняка правы, но я даже не могу найти времени чтобы поподробнее об этом поговорить, хотя очень бы хотелось… 🙁 поскольку в MYSQL я не силен.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Можно использовать язык разметки Markdown

Protected by WP Anti Spam