Как выбрать записи дубли которых присутствуют в таблице?

[que_bunt]

Как выбрать записи дубли которых присутствуют в таблице?

Здраствуйте.

Работаю над доской объявлений, счас на доске более 20к объявлений.
Где-то 2-3к это дубли одних и теж же объявлений, добавлены несколько раз.

Не подскажите каким запросом можна выбрать например все дублирующиеся записи?
(тоесть нужно что-то противополоденое до DISTINCT)

пример таблицы:

id          name       email
1           aaa          aaa(а)mail.ru
2           bbb          bbb(а)mail.ru
3           cccc          cccc(а)mail.ru
4           aaa          aaa(а)mail.ru
5           ddd          ddd(а)mail.ru
6           cccc          cccc(а)mail.ru
7           aaa          aa2(а)mail.ru

соответственно после запроса нужно получить выборку:

id          name        email
4           aaa          aaa(а)mail.ru
6           cccc          cccc(а)mail.ru

Тоесть если есть две и более записи в которых одинаковый емейл и название то одну из них оставляем, остальные надо добавит в выборку... %)
"7 aaa aa2(а)mail.ru" не должно входить в выборку так как емейл уже другой чем в предыдущих записях с именем ааа.

Буду благодарин за совет.

 

[Gas]

[sql]
select id,name,email from
(select id,name,email from tbl group by concat(name,email) having count(*)>1) as t1
join tbl as t2 on t1.name=t2.name and t1.email=t2.email and t1.id<>t2.id
[/sql]

чё-то типа такого (не проверял)

 

[MuXaJIbI41981]

HAVING смотри

 

[Wicked]

[sql]select distinct
tbl1.*
from
tbl as tbl1
inner join tbl as tbl2 on (
tbl1.id > tbl2.id
and tbl1.email = tbl2.email
and tbl1.name = tbl2.name
)[/sql]

 

[Gas]

Wicked
+1

 

[que_bunt]

Wicked это 5! Спасибо.
Но у меня phpMyAdmin после этого запроса виснет, запрос к базе поступает, а назад нет ответа уже нет...

 

[Wicked]

que_bunt
что говорят:
1) explain. В худшем случае этот запрос заставит базу перебирать 400 млн рядов, что довольно много
2) show processlist;
?

 

[que_bunt]

[sql]
EXPLAIN SELECT DISTINCT ads1. *
FROM ads AS ads1
INNER JOIN ads AS ads2 ON ( ads1.id > ads2.id
AND ads1.email = ads2.email
AND ads1.name = ads2.name )
[/sql]

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1	SIMPLE	ads2	ALL	PRIMARY,name	NULL	NULL	NULL	23631	Using temporary
1	SIMPLE	ads1	ALL	PRIMARY	NULL	NULL	NULL	23631	Range checked for each record (index map: 0x1)

-~{}~ 02.11.07 19:36:

2) show processlist;

у меня нет доступа к серваке что запустить, есть только пхпмайадмин.

 

[kruglov]

que_bunt

у меня нет доступа

Это mysql-запрос

 

[que_bunt]

kruglov спасибо.

итак что я получил:
запускаю
[sql]
SELECT DISTINCT ads1. *
FROM ads AS ads1
INNER JOIN ads AS ads2 ON ( ads1.id > ads2.id AND ads1.email = ads2.email AND ads1.name = ads2.name )
[/sql]
phpmyadmin конечно не отвечает.

создаю новое окно в котором запускаю phpmyadmin - не ответа с сервера, запускаю просто сайт - сайт лежит, возвращает пустую страницу мне... %)

 

[Mols]

Ну Вы блин даёте (с). У Вас explain ясно написал, что у Вас происходит .... заставьте сервер использовать индексы. Предварительно конечно посмотрите какие индексы вам выгодны для объединения.

 

[Wicked]

Mols
Хотите сказать, что они не используются?

que_bunt
А есть возможность протестить на другом сервере (который не жалко убить )? И можно ли дамп выложить, чтобы мы поигрались? Мыло можешь захэшировать.

PS: версия mysql какая?

 

[Mols]

Wicked
Из того, что показал explain - конечно не используются. Там же "по русски" написано )))

 

[que_bunt]

Mols индекс используеться для только для фуллтекстового поиска по полям name,text,address.

думаете что стоить поставить индекс на name+mail, сделать запрос на удаление дублей и снять этот индекс?


Wicked

А есть возможность протестить на другом сервере (который не жалко убить )?

да на всех серверах куда есть доступ какие-то сайты.
да и ладно уж убить... я после твоего запроса доску ложил на 15 минут ))

И можно ли дамп выложить, чтобы мы поигрались?

извени, этого никак...

версия mysql какая?

4.1

 

[Wicked]

que_bunt
погоди... а у тебя есть хоть один из следующих индексов (в порядке возрастания предпочтения)
* одинарный: name или email
* двойной: name+email в любом порядке
* тройной: (name+email в любом порядке) + id
?

-~{}~ 04.11.07 20:13:

4.1

4.1 вроде ок. Я точно не помню, но это вроде у 4.0 были какие-то проблемы с дожином по варчарам...

-~{}~ 04.11.07 20:17:

Из того, что показал explain - конечно не используются. Там же "по русски" написано )))

Действительно похоже на то.
Я только сейчас понял, что "Range checked for each record (index map: 0x1)" - это проверка по id, а не по name или email

 

[que_bunt]

que_bunt
погоди... а у тебя есть хоть один из следующих индексов (в порядке возрастания предпочтения)
* одинарный: name или email
* двойной: name+email в любом порядке
* тройной: (name+email в любом порядке) + id
?

Wicked нет, такого индекса нет. теперь я понял, надо сначала сделать индекс name+email, и потом делать запрос. Попробую...

 

[nail]

Keep it simple and stupid

$res = query("select id,name,email from table order by name,email");
$row1 = fetch($res);
while ($row = fetch($res))
{
   if ($row1['name'] == $row['name'] && $row1['email'] == $row['email'])
     echo $row['id'];
   $row1 = $row;
}

(тут не надо делать джоин, а надо 1 фулл скан)