✔ Doppelte Einträge löschen

[Sprint]

Hallo zusammen,

ich habe hier zwar schon einiges zu dem Thema gefunden, aber mein Problem liegt etwas anders. Ich habe eine Tabelle, in die seit Jahren Logins der Kunden protokolliert werden. Dabei sind viele Einträge doppelt, da aus irgendeinem Grund die Login Versuche immer doppelt eingetragen wurden. Ich will da jetzt was umbauen und wollte einen Index auf die Tabelle legen. Das läßt der Server natürlich nicht zu, da doppelte Einträge vorhanden sind.

Jetzt wollte ich mit dieser Anweisung die doppelten Einträge finden bzw. löschen

SELECT zeit, email FROM logins WHERE EXISTS (
    SELECT zeit FROM logins dbl WHERE logins.zeit = dbl.zeit AND logins.email = dbl.email
) ORDER BY email;

DELETE FROM logins WHERE EXISTS (
    SELECT zeit FROM logins dbl WHERE logins.zeit = dbl.zeit AND logins.email = dbl.email
);

Wenn ich das jetzt über PHPMyAdmin abschicke, bekomme ich nach einiger Zeit einen Fehler 504 und passiert ist scheinbar nichts. Die Tabelle hat etwas über 530.000 Einträge.

Gibt es eine bessere Möglichkeit, die doppelten Einträge zu löschen? Ich möchte ungern die Tabelle in kleine Pakete aufteilen und die dann bereinigen.

 

[bdt600]

Hast du Zugriff auf die Command-Line des Servers? Wenn man solche Dinge direkt per MySQL machen kann, dann kommt es auch nicht zu einem TimeOut

 

[Sprint]

Hast du Zugriff auf die Command-Line des Servers? Wenn man solche Dinge direkt per MySQL machen kann, dann kommt es auch nicht zu einem TimeOut

Nein, leider nicht

 

[Yaslaw]

MySQL? Oder oracle?

Und setze einen Index (kein Unique) auf email & zeit

Nachtrag:
Und das SQL löscht doppelte? Nach meienm Verständnis löschtg das alles in der Zeitperiode

 

[Sempervivum]

Und das SQL löscht doppelte? Nach meienm Verständnis löschtg das alles in der Zeitperiode

Den Eindruck hatte ich auch, als ich das SQL gelesen habe. Hier bei Stackoverflow werden jedoch Alternativen angeboten, die jeweils einen der Mehrfacheinträge stehen lassen:
How to delete duplicates on a MySQL table?

 

[Zvoni]

Abhängig von der MySQL-Server-Version (>=8), arbeite ich lieber mit ROW_NUMBER in einer CTE um doppelte Einträge zu finden. Ist dann nämlich Käse-einfach

Ungetestet

WITH
    DBL AS (SELECT Zeit, EMail,
        ROW_NUMBER() OVER(PARTITION BY Zeit, EMail ORDER BY EMail, Zeit) AS RN
        FROM Logins)

DELETE Logins FROM Logins AS LOG
INNER JOIN DBL ON LOG.Zeit=DBL.Zeit AND LOG.EMail=DBL.EMail
WHERE DBL.RN>1

Grund: Auf diese Art kann ich einfach mit dem SELECT des CTE vorher testen, ob es mir überhaupt die "doppelten" Einträge korrekt identifiziert für die ROW_NUMBER.
Alle doppelten Einträge erhalten nämlich eine ROW_NUMBER grösser 1

Achtung: Gezeigte Variante behält den ältesten Eintrag.
Willst du den neusten Eintrag behalten, einfach den Order By der ROW_NUMBER auf DESC setzen

EDIT: Hab gerade bemerkt, dass das nur mit den Feldern Zeit und EMail nicht funktionieren wird.
Hast du in Logins einen Primär-Schlüssel? Sowas wie ID als Auto-Increment?
Oder irgendein anderes Feld, was UNIQUE ist
Weil dann wirds einfach

WITH
    DBL AS (SELECT ID, Zeit, EMail,
        ROW_NUMBER() OVER(PARTITION BY Zeit, EMail ORDER BY EMail, Zeit) AS RN
        FROM Logins)

DELETE Logins FROM Logins AS LOG
INNER JOIN DBL ON LOG.ID=DBL.ID
WHERE DBL.RN>1

 

[Sprint]

Guten Morgen,

erst einmal vielen Dank so weit für eure Beiträge.

@Yaslaw: Das ist eine MariaDB Datenbank und du hast Recht, das löscht alles. Ich habe mal 5000 Datensätze auf meinen lokalen Rechner kopiert und die waren alle weg.

@Zvoni: Eine laufende Nummer war nicht vorhanden, hab ich dann aber ergänzt. Ich habe dein Query dann mal ausprobiert, bekomme aber einen Fehler auf Zeile 6. Ich muß aber auch gestehen, daß ich im Moment noch nicht so ganz verstehe, was da passiert.

@Sempervivum: Dein Link zu dem Stackoverflow Artikel hat mir eine sehr einfache und bis jetzt auch sehr schnelle Lösung präsentiert.

CREATE TABLE logins_temp AS
SELECT * FROM logins GROUP BY zeit, email;

DROP TABLE logins;
RENAME TABLE logins_temp TO logins;

Ein Test mit den 5000 Datensätzen war selbst auf meiner im Vergleich sehr langsamen Serveremulation in fast Nullzeit erledigt.

 

[Zvoni]

@Zvoni: Eine laufende Nummer war nicht vorhanden, hab ich dann aber ergänzt. Ich habe dein Query dann mal ausprobiert, bekomme aber einen Fehler auf Zeile 6. Ich muß aber auch gestehen, daß ich im Moment noch nicht so ganz verstehe, was da passiert.

Wie gesagt: Ungetestet.
Aber da du es ja anscheinend gelöst bekommen hast.....
Übrigens: Sehr sehr vorsichtig sein mit deiner Lösung.
Ich hoffe mal es gab keine Detail-Tabelle zu "Logins".....

und was Fehler in Zeile 6 betrifft:
Wird wohl sein, dass ich den Tabellenname statt des Alias verwendet habe
Auch ungetestet

WITH
    DBL AS (SELECT ID, Zeit, EMail,
        ROW_NUMBER() OVER(PARTITION BY Zeit, EMail ORDER BY EMail, Zeit) AS RN
        FROM Logins)

DELETE LOG FROM Logins AS LOG
INNER JOIN DBL ON LOG.ID=DBL.ID
WHERE DBL.RN>1

 

[Sprint]

Nein, eine Detail-Tabelle gab es nicht. Ist eine ganz einfache, "dumme" Tabelle, die einfach nur die Logins und Login-Versuche speichert. Ist bis jetzt auch noch nie gebraucht worden. Da aber Daten die Grundlage unseres Geschäfts ist, muß natürlich auch das gespeichert werden.

Auch auf inzwischen über 600k Einträge hat die Lösung mit der temporären Tabelle innerhalb kürzester Zeit funktioniert. Nochmal Danke an alle.