MySql LEFT JOIN zu langsam

[Mark]

So langsam blicke ich durch mit dem JOIN, nur stosse ich jetzt an die Limits.

Ich brauche eine Abfrage, die doppelte Einträge über 4 Spalten findet.
Ohne JOIN also etwa so:

$abfrage = mysql_query("SELECT * FROM `tabelle`");
while($row = mysql_fetch_object($abfrage))
{
     $abfrage2 = mysql_query("SELECT * FROM `tabelle` WHERE `id` != ".$row->id." AND `feld1` = '".$row->feld1."' AND `feld2` = '".$row->feld2."' AND `feld3` = '".$row->feld3."' AND `feld4` = '".$row->feld4."'");
     while($row2 = mysql_fetch_object($abfrage2))
     {
          print $row->id." = ".$row2->id."<br>";
     }

}

Das dauert bei 1000 Datensätzen knapp 60 Sek. und damit doppelt so lange wie die max. Script runtime meines Providers.
Schneller gehts mit JOIN, dazu hatte mir ein freundlicher Helfer hier schon mal die Syntax gegeben:

SELECT n1.`id` source_row, n2.`id` same_as_row FROM `tabelle` n1 JOIN `tabelle` n2 ON n1.`feld1` = n2.`feld1` AND n1.`feld2` = n2.`feld2` AND n1.`feld3` = n2.`feld3` AND n1.`feld4` = n2.`feld4` AND n1.`id` != n2.`id`;

Damit bin ich klar gekommen, bis etwa 10K Datensätze, ab da braucht auch diese Abfrage mehr als 30 Sek.
Ich habe aber bis zu 100K Datensätze, sprich ich muss das entweder zigfach splitten, oder ich brauche eine schnellere Abfrage.

Hat jemand eine Idee?

 

[mermshaus]

Kurze Frage vorab: Welche Indizes hast du für die Tabelle gesetzt?

Wie definierst du, welcher Datensatz source_row ist und welcher same_row? Brauchst du die Angaben?

Was passiert, wenn ein Datensatz dreifach vorkommt?

Deine Query in lesbar:

SELECT
        n1.`id` AS source_row,
        n2.`id` AS same_as_row
FROM
        `tabelle` AS n1
INNER JOIN
        `tabelle` AS n2
    ON
        n1.`feld1` = n2.`feld1`
    AND 
        n1.`feld2` = n2.`feld2`
    AND
        n1.`feld3` = n2.`feld3`
    AND
        n1.`feld4` = n2.`feld4`
    AND
        n1.`id` != n2.`id`

Hier ein erstes Fragment einer Lösung mit GROUP BY:

(SELECT
        `inner`.`id`,
FROM
        `tabelle` AS `inner`
GROUP BY
        `inner`.`feld1`,
        `inner`.`feld2`,
        `inner`.`feld3`,
        `inner`.`feld4`
HAVING
        COUNT(*) > 1)

 

[Mark]

Es gibt nur die ID als Primärschlüssel, alle anderen Felder sind Text.
Ich brauche überhaupt nichts vom Inhalt, ich muss nur wissen, ob und welche Zeilen in allen 4 Spalten identisch sind.
Dreifach ergibt sich von alleine, weil es in doppelt enthalten ist.

Gerade ausprobiert, mit deinem Code dauert das bei 47K Datensätzen 4 Sek., schon VIEL besser.

Danke.

 

[sysop]

Baust du die Tabelle neu auf oder ist das eine Auswertung, die öfter ausgeführt wird?

Sollte letzteres der Fall sein, könntest du mit Limit arbeiten, die Grenzen speichern, die Laufzeit messen und das Script per reload einfach neu starten. Dann setzt du einfach dort wieder auf, wo du aufgehört hast.

 

[Mark]

Ich baue die Tabelle neu auf.
Ich brauche diese Abfrage nur zur Prüfung, die mache ich manuell im PHPMyAdmin.
Sinn der Sache ist, dass es neben der unique ID keinen Datensatz geben darf, der in allen 4 Feldern identisch ist.

 

[mermshaus]

Du könntest die Abfrage sicherlich weiter beschleunigen, wenn du auf mindestens eins der vier Felder einen Index setzen würdest. Das ist aber schwierig genauer zu sagen, ohne weitere inhaltliche Informationen zu haben und zum Beispiel auch die Beschaffenheit der Daten zu kennen. Theoretisch könntest du auch einen UNIQUE-Index über alle vier Spalten setzen, was effektiv verhindern würde, dass in der Hinsicht doppelte Datensätze überhaupt angelegt werden können. Das geht aber wiederum nicht (oder zumindest nicht besonders gut – meines Wissens), wenn eine der Spalten von einem „großen“ Datentyp wie TEXT ist.

Das hängt theoretisch auch leicht davon ab, ob du für möglichst schnelle INSERT-Operationen sorgen musst. Anders ausgedrückt: Wenn du 100.000 Datensätze auf einmal anlegst, ist nicht auszuschließen, dass ein komplizierter Index diesen Vorgang spürbar verlangsamt.

 

[Mark]

Im Moment mache ich INSERT manuell im PHPMyAdmin, max. 500 auf einmal.
Export aus einer alten Tabelle, ein paar kleine Änderungen, dann INSERT in die neue Tabelle.
Da liegt auch das Problem, weil ich dabei immer mal wieder Tippfehler baue, darum will ich die Tabelle zwischendurch immer mal wieder prüfen.

Im Moment wäre es mir noch egal, wie lange dieser INSERT dauert, aber wenn das Ganze fertig ist kommen weitere INSERT von einem HTML Formular.
Einen Index über die 4 Spalten brauche ich dann nicht mehr, darum wäre es unsinnig den jetzt zu erstellen.
Das Script dazu prüft die Formulardaten, da können keine doppelten Einträge entstehen, dafür brauche ich an der Stelle möglichst schnelle INSERT, weil das Formular bis zu 700 INPUT Felder hat, die je bis zu 1K Text enthalten können, da braucht das Script zum Prüfen schon fast 10 Sek.

 

[mermshaus]

When in doubt, Use The Index, Luke!

Disclaimer: Ich habe bisher nichts davon gelesen, aber ich meine mich an eine Diskussion auf php.de oder so zu erinnern, in der durchschimmerte, dass die Seite ganz gut ist.

 

[Mark]

Klar, wenn es nochmal zuviel wird, komme ich drauf zurück.
Im Moment bin ich mit der GROUP BY Lösung voll zufrieden.
4 Sek. für 47K Zeilen ist in Ordnung, das müsste auch bei 100K noch unter 30 Sek. bleiben.

Danke nochmal.