MYSQL выбрать строки с дублями полей

убрать group by?

Не GROUP BY, а SORT BY.

уберёшь group by - не будет работать having.
либо добавь order by filepath, либо используй where и sort by.

Тогда не будет работать having

не слушай анонимусов

SELECT * FROM `files` 
WHERE `filepath` IN (
SELECT `filepath` FROM `files` 
GROUP BY `filepath` 
HAVING COUNT(`filepath`) > 1
)

надеюсь, с синтаксисом не напутал

http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql

Вместо него WHERE, как анонимус выше посоветовал.

В старых мускулях вложенные запросы могут работать довольно медленно (не использовать индекс). Если запрос не одноразовый то перед тем как его использовать стоит посмотреть план исполнения (explain select ...)

Верно, тупо уходит в аут MYSQL.

А база сильно большая? Если там не миллионымиллиарды строк то при фулскане должен просто хрустеть хардом несколько секунд/минут.
Версия мускуля какая?

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

База ~100mb, эта таблица 28276 строк.

Версия сервера: 5.5.35-log - MySQL Community Server (GPL)

И запрос намертво виснет? А есди подождать минуту другую? Покажи план выполнения запроса (допиши перед запросом explain).

Можно сперва встречный вопрос, а зачем их надо найти? Если просто надо дропнуть все дубликаты для данной записи, то находить их для этого не надо.

Еще можно пометить все дубликаты с использованием дополнительной колонки и джойна (http://www.mysqltutorial.org/mysql-update-join/). Задно сразу отличить дубликат от оригинала.

Надо дропнуть. Спасибо, дома почитаю!

Виснет намертво, час ждал, бесполезно. Попробую с explain уже дома, спасибо за помощь!

mysql> EXPLAIN SELECT * FROM `files` WHERE `filepath` IN ( SELECT `filepath` FRO M `files` GROUP BY `filepath` HAVING COUNT(`filepath`) > 1 ) -> ;

+----+--------------------+-------+------+---------------+------+---------+------+-------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+--------------------+-------+------+---------------+------+---------+------+-------+---------------------------------+

| 1 | PRIMARY | files | ALL | NULL | NULL | NULL | NULL | 28276 | Using where |

| 2 | DEPENDENT SUBQUERY | files | ALL | NULL | NULL | NULL | NULL | 28276 | Using temporary; Using filesort |

+----+--------------------+-------+------+---------------+------+---------+------+-------+---------------------------------+

2 rows in set (0.00 sec)

А индексы-то в таблице есть?
Показывай SHOW INDEX FROM files;
Свободного места на диске сколько?

mysql> SHOW INDEX FROM files;

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| files | 0 | PRIMARY | 1 | fid | A | 28276 | NULL | NULL | | BTREE | | |

| files | 1 | uid | 1 | uid | A | 271 | NULL | NULL | | BTREE | | |

| files | 1 | status | 1 | status | A | 54 | NULL | NULL | | BTREE | | |

| files | 1 | timestamp | 1 | timestamp | A | 28276 | NULL | NULL | | BTREE | | |

+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.01 sec)

Места полно. 600Гб.

В старых мускулях вложенные запросы могут работать довольно медленно

И в новых тоже. Иной раз на совсем простой таблице SELECT .. WHERE ... IN (SELECT ..) зависает на десятки секунд. В то время, как JOIN отрабатывает за тысячные доли секунды. И даже если JOIN неприменим, то создание временной таблицы и выборка через JOIN уже с ней срабатывает за десятые-сотые доли секунды. Просто воспринимаю это как данность :D

Попробуй так:

EXPLAIN SELECT t1.id, t1.filepath
  FROM files t1,
  (SELECT filepath
    FROM files
    GROUP BY filepath
    HAVING COUNT(filepath) > 1
  ) t2
  WHERE t1.filepath = t2.filepath

mysql> EXPLAIN SELECT t1.fid, t1.filepath FROM files t1, (SELECT filepath FROM f iles GROUP BY filepath HAVING COUNT(filepath) > 1 ) t2 WHERE t1.filepath = t2.fi lepath;

+----+-------------+------------+------+---------------+------+---------+------+-------+---------------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+------------+------+---------------+------+---------+------+-------+---------------------------------+

| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1315 | |

| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 28276 | Using where; Using join buffer |

| 2 | DERIVED | files | ALL | NULL | NULL | NULL | NULL | 28276 | Using temporary; Using filesort |

+----+-------------+------------+------+---------------+------+---------+------+-------+---------------------------------+

3 rows in set (0.61 sec)

В последних версиях (то-ли с 5.5 то-ли с 5.6) что-то в оптимизаторе подкрутили на этот счёт, в каких-то случаях подапросы стали работать так-же быстро как джоины, а в некоторых случаях быстрее. На хабре был пост, искать лень.
Но в целом да, с подзапросами есть какая-то попа.

А в данном случае попа усиливается тем что по столбцу filepath нет индекса.

Короче для начала нужно добавить индекс (если запрос не одноразовый), потому-что без индексов жить в принципе грустно. А потом ещё и переписать запрос на join, если всё-равно дико тормозить будет.

Без индекса — всё тлен.
Мускуль пытается сортировать по filepath, а поскольку индекса там нет, а данных там много то сортировать он пытается в файле, и почему-то от этого запрос виснет намертво.

А в /tmp? Гюльчитай, покажи df -h

В последних версиях (то-ли с 5.5 то-ли с 5.6) что-то в оптимизаторе подкрутили на этот счёт

5.6 не видел, в 5.5 и MariaDB 10.0 (я м MySQL 5.5 сразу на неё перелез) проблема актуальна.

А в данном случае попа усиливается тем что по столбцу filepath нет индекса.

Ну, это совсем грустно :)

Короче для начала нужно добавить индекс (если запрос не одноразовый)

Самое смешное, что в случае подзапросов часто выгодно создать временную таблицу, добавить там индекс и уже по ней делать JOIN. Нередко получается не сопоставимая разница, вместо десятков секунд или минут — десятые доли секунды на всё, включая создание временной таблицы и индексов.

Нет, мне один раз решить проблему, обновляю CMS Drupal с 6 до 7, начались проблемы при переносе полей нод, не переносятся пути изображений, накопал что проблема с этой таблицей.

Проблема застарела, видно еще в 2010 году был некий косяк с базой. Надо ее один раз почистить и все.

WAMP, место есть, это копия сайта у меня на нойте.

Сейчас уже не вешает запрос?

Можно еще добавить индекс для `filepath`

ALTER TABLE `files` ADD INDEX(`filepath`);

Тогда делай временную таблицу, или гугли как переписать подапрос на join, про это написано много

Мускуль пытается сортировать

Там нет сортировки.

а данных там много

28276 строк, не много.

индекса там нет

Согласен, что индекс может помочь, но не факт.

Ты её не видишь, и я её не вижу, а она есть:

Using temporary; Using filesort

Ты её не видишь, и я её не вижу, а она есть:

Using temporary; Using filesort

Я не об этой сортировке. Если добавить order by будет еще медленнее.

Вот, ты её даже в виду не имеешь, а она всё-равно есть и всё-равно тормозит выполнение запроса вплоть до полного его зависания.

Запрос отработал как надо, спасибо огромное!

Спасибо большое за помощь!

точнее так: ALTER IGNORE TABLE ADD UNIQUE(`filepath`) и дубликатов нет

Согласен. В данном случае UNIQUE будет актуальнее (только имя таблицы забыл). Но запись в таблицу через INSERT IGNORE придется делать.

Читай выше, задача была дропнуть дубликаты. После удаления дубликатов можно или оставить юник и забыть про дубликаты навсегда или дропнуть юник и вернуться к прежнему ссостоянию но без дубликатов

INSERT IGNORE не нужен в любом случае.

SELECT * FROM (

SELECT * , COUNT( vId ) AS count FROM t_sklad_update u GROUP BY u.vId )u2 WHERE u2.count >1