Dubletten Prüfung dauert zu lange.

[leoo]

Hallo Leute!

Hab ein Problem bei der Dublettenprüfung. Habe es gemacht wie im Artikel unter: http://www.selfphp.com/forum/showthr...?threadid=8947 beschrieben. Leider dauert die Abfrage zu lange

Das Select Statement:

SELECT t1.kid AS id1, t2.kid AS id2 FROM k_data AS t1 INNER JOIN k_data AS t2 on t1.email = t2.email WHERE t1.kid < t2.kid

kid = Primary Key und Index gesetzt
email = Index gesetzt

Space Usage Tabelle: 10.447 KB
Tabelle: 51003 Datensätze

Hat von euch wer eine Idee was ich falsch mache oder gibt es eine gute Doku zur Dublettenprüfung? Bin für jede Hilfe dankbar.

Lg Stefan

[diver-network]

Hi Stefan,

leider hast Du meinen Tip nicht richtig durchgelesen.
Das

...WHERE t1.primary-key < t2.primary-key // WICHTIG, sonst Kreuzprodukt aus allen Inhalten und quasi Endlosschleife!

ist wirklich wichtig.
Füg' das mal entsprechend angepasst an Dein Statement an und schon sollte es bei richtig gesetzten Indizes in einer akzeptablen Zeit funktionieren.

Gruß,

Andy

[leoo]

Hi!

Ups hat irgendwie nicht alles kopiert hab es jetzt in mein Posting reingegeben habe die Abfrage mit den Primary-Key gemacht.

SELECT t1.kid AS id1, t2.kid AS id2 FROM k_data AS t1 INNER JOIN k_data AS t2 on t1.email = t2.email
WHERE t1.kid < t2.kid

lg stefan

[diver-network]

Hi Stefan,

da Deine Abfrage stimmt und ich mal davon ausgehe, daß die Indizes wirklich richtig gesetzt sind weiß ich auch nicht weiter.

Bei mir funktioniert die Abfrage normalerweise in weniger als einer Minute bei vergleichbaren Datenmengen. Wenn es länger dauert habe ich entweder die WHERE id1 < id2 Klausel vergessen oder keinen Index gesetzt.

Eventuell hilft es Dir, wenn Du vor dem SELECT noch ein EXPLAIN machst und da mal nachschaust, ob und wenn ja welche Indizes MySQL verwendet.

Ansonsten noch folgende Fragen bzw. Anregungen, warum es lange dauern kann:
- Was verstehst Du unter "zu lange"? 2 Minuten kann die Abfrage schon dauern, 10 Minute wäre aber wirklich zu langsam.
- Wieviele Doubletten gibt es schätzungsweise? (je mehr, je langsamer)
- Wieviele davon kommen mehr als 2mal vor? (je öfters, je langsamer)
- laufen noch andere Abfragen parallel oder ist Dein Statement das Einzige, daß dann die DB belastet? (am wichtigsten!)

Eine allgemeine Regel, wie man auf doppelte Datensätze prüfen kann bzw. soll kenne ich nicht, bin aber für bessere Mittel und Wege auch immer dankbar.

Gruß,

Andy

[leoo]

Hi!

Wenn ich ein EXPLAIN vorschicke kommt folgendes:

Link: http://www.leoo.at/explain1.pdf Stimmt da was nicht?

Was verstehst Du unter "zu lange"? 2 Minuten kann die Abfrage schon dauern, 10 Minute wäre aber wirklich zu langsam.

Nach 15 Minuten breche ich im phpMyAdmin ab

Wieviele Doubletten gibt es schätzungsweise? (je mehr, je langsamer)

ca. 200

Wieviele davon kommen mehr als 2mal vor? (je öfters, je langsamer)

ca. 10

laufen noch andere Abfragen parallel oder ist Dein Statement das Einzige, daß dann die DB belastet? (am wichtigsten!)

nein

lg stefan

[diver-network]

Hi Stefan,

soweit ich sehen kann ist Dein SELECT in Ordnung und der Output von EXPLAIN ist auch ok. Der Key t1.email wird verwendet, so daß Du kein Kreuzprodukt hast.
Dennoch muß MySQL ca. 150.000 (genauer: 51.003 * 3) rows (Zeilen, Datensätze) anpacken, um zum gewünschten Ergebnis zu kommen. Dies dauert natürlich eine Weile.

Da ich kein MySQL Profi bin bin ich jetzt auch am Ende meines Wissens angelangt und kann Dir nicht mehr weiter helfen. Hier hoffe ich, daß noch andere diesen Thread verfolgen und besser Bescheid wissen.

Die einzigen Mittel, die mir noch einfallen sind:
- Mache vorher ein OPTIMIZE TABLE k_data, um die Keys besser zu sortieren.
- Versuche, die mögliche Treffermenge zu reduzieren, z.B., indem Du zuerst als zusätzliche Einschränkung ... WHERE t1.kid < t2.kid AND t1.kid < 25000 und in einem zweiten Schritt ... AND t1.kid > 24999 (NICHT 25000, sonst fehlt das) bringst oder z.B. WHERE email LIKE "a%" zustäzlich bringst und das Ergebnis dann auf mehrere Male bekommst.

Wenn Du nur die doppelten Einträge eliminieren willst könntest Du auch eine zweite Tabelle anlegen und diese mit

SELECT DISTINCT(t1.kid, t1.email) FROM k_data AS t1 INTO k_data_temp

füllen. BEACHTE ABER, daß meine Syntax hier nicht stimmen muß, da "freihändig" geschrieben.

Wie geschrieben kann ich Dir momentan nicht mehr weiter helfen, werde aber heute abend (habe gerade kein MySQL zur Hand) ein paar Dinge versuchen und mich eventuell noch mal melden.

Gruß und viel Erfolg,

Andy

[diver-network]

Hi Stefan,

leider habe ich nichts besseres gefunden.
Ein

SELECT t1.kid, t1.email
  FROM k_data AS t1
 GROUP BY t1.email
HAVING COUNT(t1.email) > 1 // bzw. COUNT(*)

hat leider auch nicht das gebracht, was ich erhofft hatte.

Sorry,

Andy

[diver-network]

Hi zusammen,

zum finden von doppelten Einträgen in einer Tabelle hatte ich noch einmal eine Anfrage in der Newsgroup mailing.database.mysql gestartet ("Best way for finding duplicate entries in table?" vom 1.Juni 2005).
Anbei die von mir übersetzte Antwort DES MySQL Spezialisten (meine Meinung) der Newsgruppe, Bill Karwin.
Ihm gebührt der Dank für diese Lösung.

Liste der Einträge, die mehrfach in der Tabelle auftauchen.

SELECT t2.`field`
FROM `table` AS t2
GROUP BY t2.`field`
HAVING COUNT(t2.`field`) > 1;

Liste der IDs von Einträgen, die mehrfach in der Tabelle auftauchen.

SELECT t.id
FROM `table` AS t
WHERE t.`field` IN (
SELECT t2.`field`
FROM `table` AS t2
GROUP BY t2.`field`
HAVING COUNT(t2.`field`) > 1);

Liste der jeweils kleinesten ID von Einträgen, die in einer Tabelle vorkommen.

SELECT MIN(t3.id)
FROM `table` AS t3
GROUP BY t3.`field`;

Alles zusammen gesetzt sollte unten stehendes Statement alle Datensätze löschen,
die mehr als einmal in einer Tabelle vorkommen.
Hierbei wird der Eintrag mit der kleinsten ID nicht gelöscht.

DELETE FROM `table`
WHERE id IN (
SELECT t.id
FROM `table` AS t
WHERE t.`field` IN (
SELECT t2.`field`
FROM `table` AS t2
GROUP BY t2.`field`
HAVING COUNT(t2.`field`) > 1))
AND id NOT IN (
SELECT MIN(t3.id)
FROM `table` AS t3
GROUP BY t3.`field`);

Sollte der Eintrag mit der größten ID des Feldes stehen bleiben so ist das Statement MIN(t3.id) in MAX(t3.id) zu ändern.

Um ohne SUB- SELECTS automatisch doppelte Einträge zu löschen kommt man um einige Handarbeit leider nicht rum, man kann aber sicherlich einiges von oben wieder verwerten und z.B. Zwischenergebnisse entweder in eine Temporäre Tabelle einfügen oder aber in eine Datei ausgeben (Stichwort SELECT INTO OUTFILE), die man dann mit einem Texteditor anpassen kann.

HTH,

Andy