Find Duplicates

Kak naj-dobre (ot optimizacionna gledna tochka) trqbwa da se napishe script, koito namira wsichki duplicirashti elementi w dadeni tablici (ama swyrzani, t.e. red, koito moje da e dupliciran, e systawen ot izbrani poleta ot tablicite, koito ot swoq strana sa swyrzani chrez foreign keys)?
Stava vypros za MySQL (zatowa script), tyj kato wlojeni slojni select-owe ne mogat da se pishat (nito da se zamestqt s nqkolko).

Ne shvanah.
Kakvo se opitvash da napravish?
Prilicha mi na kofti design.
Dai edin primer

Linux is like a wigwam - No Windows, No Gates, Apache inside

Ami predstawi si tablica, chiito unikalno pole e samo PK-to (t.e. autonumber-a) i koqto e swyrzana s drugi tablici, pritejavashti informaciqta, praweshta tezi poleta unikalni (MySQL nqma referenci i takiwa extri).
Po-princip, primerno w access i drugi podobni, ima direktno zaqwka za find duplicates, no tam se poddyrjat slojni wlojeni select-i i wsichko e ok (makar i samo za edna tablica).
Ta az se interesuwam ot dobyr algoritym, wkljuchwasht PHP, prawesht towa za 1 ili poveche tablici.

Ami pak nestho ne razbrah. Ti kak si s SQL?
Az ot dosta godini se zanimavam i ako dadesh primer shte ti sglobia edin-dva select-a
Imash stranni razbirania za 'duplicates'
Ne znam (a i ne me interesuva) kakvo razbirat v microsoft pod 'find duplicates' no v ANSI SQL takava definicia niama :)
Ta kazhi za foreign key li govorim? Za count(distinct col) li? ili kakvo?

Obiknoveno edin 20-30 redov SELECT rabodti mnogo po-burzo ot select * ; i posle da go obrabotvash v PHP(perl, c, kakvoto i da e)

Taka che dai primer.
tbl1 id primary key;
tbl2 - ??
tbl3 - ??

Linux is like a wigwam - No Windows, No Gates, Apache inside

btw. vaprosa e za klub bazi danni :)
A men mozhesh da me namerish na
email: [email protected]
ICQ: 10443218
IRCbg - #mysql
EFNet - #mysql - Tam e daleche :))

Malko linkove:
[линк]
[линк]
[линк]
docs.online.bg/DB/SQL_Draft/sql1992.txt
mysql.online.bg/documentation/mysql/bychapter/
[линк]
Poslednoto e dosta cenno

Linux is like a wigwam - No Windows, No Gates, Apache inside

:) Dobre sym i s SQL-a i s PHP-to. Prosto qwno ne se izrazqwam dobre. Prichinata e w typiq MySQL i w towa, che sym se zanimawal predimno s Oracle.
Mejdu drugoto, towa, koeto iskam, ne stava samo sys zaqwki. Zatowa go post-nah tuka (pyk i BD kluba e malko umrql). Towa, che ne stava samo sys zaqwki, go ustanowih chrez opiti i naj-nakraq s razni postingi w mysql-lista (i to ot Monty).

Problemyt, w primerni e sledniq (ste go dam za edna tablica samo, shtoto za poveche pak nqma da se doobqsna kato horata).
table1 (dawam primer, tyj che ne mi razprawqjte za design na bazata,
===== design-a si e perfekten, prosto se iziskwa poveche ot bazata)
key
stocknumber
manufacturer
description
....................

celta e da se namerqt wsichki zapisi (primerno sortirani po stocknumber), koito se duplicirat po nqkakwi poleta, primerno stocknumber i manufacturer, t.e. kato imam
1 mig21 russia triygylnikrile
2 mig21 russia nestosi
trqbwa da se otkriqt wsichki takiwa zapisi (bez duplicirane pri izwejdaneto im), za da se iztriqt w posledstwie primerno. Typoto e, che i tablicata e super golqma, t.e. edin join wseki kym wseki ne wyrwi izobsto w realno wreme (chakah 2 chasa i se otkazah). Edinstweniqt nachin, spored mene, e s nqkakyw count, koito da dawa edno dobro priblijenie ot powtoreniqta i w posledstie da se chisti s nqkakyw algoritym.

btw ako pak ne sym se izqsnil wij mi icqno i se contact-ni s mene

Ami proceti v MySQL manual glava 7.29 EXPLAIN Syntax (Get Information About a SELECT)

S explain shto vidish kade i kakvo ti bavi select-a
Naistina s Mysql ne mozhe da izpolzvash:
delete from tbl where id in (select ... kakvoto i da e)
Obache ima dosta nachini da stane po-burzo.

Molia te ne izpolzvai termina 'duplicirat' :)

Da vidim dali te razbrah.
table1:
1 mig21 russia tttt
2 mig21 russia yyyy
za teb e 'povtorenie po stocknumber, manufacturer' taka li?
Kakvo shte kazhes za tova:

mysql> select * from acid;
+----+-------------+--------------+----------------+
| id | stocknumber | manufacturer | description |
+----+-------------+--------------+----------------+
| 1 | mig21 | russia | triygylnikrile |
| 2 | mig21 | russia | neshtosi |
| 3 | su27 | russia | elkjytry |
| 4 | su27 | russia | neshtosi |
| 5 | su27 | russia | rtrtytry |
| 6 | su27 | russia | yrtytr |
| 7 | mig21 | china | elkjytry |
+----+-------------+--------------+----------------+
7 rows in set (0.00 sec)

mysql> select concat(stocknumber, manufacturer) as d, count(*) from acid group by d;
+-------------+----------+
| d | count(*) |
+-------------+----------+
| mig21china | 1 |
| mig21russia | 2 |
| su27russia | 4 |
+-------------+----------+
3 rows in set (0.00 sec)



'edin join wseki kym wseki ne wyrwi' ... po princip. t.e. ne ti dava nishto novo i niama nikakuv smisal da go pravish

Linux is like a wigwam - No Windows, No Gates, Apache inside

Oshte edin primer:

mysql> select count(distinct stocknumber) as stock_count, count(distinct manufacturer) as man_count, count(distinct stocknumber, manufacturer) as comb_count, count(*) as total from acid;
+-------------+-----------+------------+-------+
| stock_count | man_count | comb_count | total |
+-------------+-----------+------------+-------+
| 2 | 2 | 3 | 7 |
+-------------+-----------+------------+-------+
1 row in set (0.00 sec)

Linux is like a wigwam - No Windows, No Gates, Apache inside

Blagodarq mnogo za sywetite, no polojenieto ne e tolkowa rozawo, kolkoto izglejda. Znachi az go test-wam wyw golqma baza (btw prez izminalata sedmica pregledah internet za ideite i problemite i te gore-dolu sywpadat s moite; info ima naj-weche w mysql lista), ta problema tam e, che s edin select s group by i having count se propuskat (ne e qsno shto) dosta zapisi. Konkateniraneto ne mi wyrshi pak rabota, tyj kato si iskam da si rabotq sys zapisite (pyk i preminawaneto kym tekstowa informaciq ne e dosta dobro otkym skorost). Ta naj-dobroto, koeto namerih i koeto mislq da prawq (mejdu drugoto, towa mi trqbwa za nabor ot tablici, a ne samo za edna) e slednoto(ste goworq za predniq primer):
prawq si edin select * from ..... order by stocknumber, manufacturer; i posle s edin php skript (linejna slojnost) minawam otgore i si zimam tezi, koito mi trqbwat, a t.e. "duplicirastite" se po tezi poleta.
Towa e dosta po-byrzo ot wsqkakwi drugi resheniq (okolo 2 pyti pone).