MySQL mit 6,2 Mio. Datensätzen

[Lars]

Hallo zusammen,

ich habe ein "kleines" Performance-Problem. Ich habe eine MySQL-Datenbank mit einer Tabelle, die mehr als 6 Millionen Datensätze beinhaltet. Dumm daran ist nur, dass einige Datensätze doppelt vorhanden sind. Jetzt habe ich versucht, per Skript die Datensätze löschen zu lassen.

<?php
echo "lese Daten: "; // Kontrollausgabe, ob der Skript überhaupt gestartet ist

$daten = mysql_db_query("datenbank","select id, name from tabelle group by name order by name") or die(mysql_error());

echo "Totalcount einzelne Firmennamen: ".mysql_num_rows($daten)."\n\n"; // Kontrollausgabe, um die Datenmenge  zu sehen

while ($wert = mysql_fetch_array($daten) )
{
    $i++;
    mysql_db_query('datenbank','delete from tabelle where name = "'. addslashes($wert[name]).'" and id != "'.$wert[id].'"') or die (mysql_error());
    echo "Name: ". $wert["name"] .", Datensatz: $i "; // erneute Kontrollausgabe, um zu prüfen, ob der Skript überhaupt noch arbeitet
}
echo "Fertig"; //Letzte Kontrollausgabe
?>

Der Skript läuft trotz Ausführung auf der Konsole sehr langsam. Hat jemand eine Idee, wie man diesen Skript optimieren/beschleunigen könnte?

Danke vorab

Lars

[_King Lully]

Der Skript läuft trotz Ausführung auf der Konsole sehr langsam. Hat jemand eine Idee, wie man diesen Skript optimieren/beschleunigen könnte?

Ja, 1.)Indizes und 2.)mengenbasierte Logik.

Mengenbasierte Logik besteht hier im Bilden der so genannten Differenzmenge, die per Vergleich der Gesamtmenge und der per "SELECT DISTINCT" gebildeten Untermenge zu erreichen ist. Die Differenzmenge besteht aus den Dubletten.

[Lars]

Ja, 1.)Indizes und 2.)mengenbasierte Logik.

Mengenbasierte Logik besteht hier im Bilden der so genannten Differenzmenge, die per Vergleich der Gesamtmenge und der per "SELECT DISTINCT" gebildeten Untermenge zu erreichen ist. Die Differenzmenge besteht aus den Dubletten.

Habe etwas Vergleichbares versucht, hat aber zu einer Speicherüberlastung geführt hatte:

<?php
$i=0;
echo "lese Daten: ";
$daten = mysql_db_query("mami2000","select id, Firmenname from adressen_2 group by Firmenname") or die(mysql_error());

echo "Daten eingelesen ";

while($entry = mysql_fetch_array($daten)) {
    $datenString .= '"'.$entry['id'].'", ';
}

$datenString = substr($datenString, 0,-2);

echo "Totalcount einzelne Firmennamen: ".mysql_num_rows($daten)."\n\n";

$sql = "Delete from adressen_2 where id NOT IN ('.$datenString.');";

mysql_db_query('mami2000', $sql);

echo "Fertig";
?>

[_King Lully]

Ja, 1.)Indizes und 2.)mengenbasierte Logik.

Mengenbasierte Logik besteht hier im Bilden der so genannten Differenzmenge, die per Vergleich der Gesamtmenge und der per "SELECT DISTINCT" gebildeten Untermenge zu erreichen ist. Die Differenzmenge besteht aus den Dubletten.

Habe etwas Vergleichbares versucht, hat aber zu einer Speicherüberlastung geführt hatte:

<?php

Lass mal den PHP-Mist weg und schreibe Dir ein paar SQL-Statements (kleiner Tipp: eins reicht ;), die den Job erledigen und implementiere dann in PHP.

[Lars]

Lass mal den PHP-Mist weg und schreibe Dir ein paar SQL-Statements (kleiner Tipp: eins reicht ;), die den Job erledigen und implementiere dann in PHP.

Ok, den "PHP-Mist" lasse ich gerne weg ;)

Aber wie soll dieses Statement aussehen? Ich dachte bisher, dass group-by-Klauseln schneller arbeiten als distinct-Anweisungen. Stehe gerade etwas auf dem Schlauch, wenn ich ehrlich sein soll. Wäre für ein gutes Statement sehr dankbar.

[_King Lully]

Aber wie soll dieses Statement aussehen? Ich dachte bisher, dass group-by-Klauseln schneller arbeiten als distinct-Anweisungen. Stehe gerade etwas auf dem Schlauch, wenn ich ehrlich sein soll. Wäre für ein gutes Statement sehr dankbar.

Mal eine Skizze:

  
DELETE  
FROM  
 DT_1  
WHERE  
 DT_1_ID NOT IN  
 (  
 SELECT DISTINCT  
  DT_1_ID               --,  
                        --  <Kriterienliste>  
 FROM  
  DT_1  
 )  

Haben jetzt wenig Zeit, scheint mir ziemlich einfach zu sein, aber vielleicht irren wir, bitte prüfen...

[Lars]

Danke schon mal vorab,

Mal eine Skizze:

DELETE
FROM
DT_1
WHERE
DT_1_ID NOT IN
(
SELECT DISTINCT
  DT_1_ID               --,
                        --  <Kriterienliste>
FROM
  DT_1
)

>   
> Haben jetzt wenig Zeit, scheint mir ziemlich einfach zu sein, aber vielleicht irren wir, bitte prüfen...  
  
Habe ich schon mal getestet. Das Problem ist das karthesische Produkt der beiden Abfragen, dieses wird zu groß für den Speicher (von der Ausführungszeit ganz zu schweigen)

[Klaus Mock]

Hallo,

Habe ich schon mal getestet. Das Problem ist das karthesische Produkt der beiden Abfragen, dieses wird zu groß für den Speicher (von der Ausführungszeit ganz zu schweigen)

Hier stellt sich dann gleich mal die Frage, wie viele Datensätze eigentlich von dem Problem betroffen sind.
Wenn es nämlich eine überschaubare größe ist, dann wäre das manualle erstellen einer Löschanweisung auch abzuwägen.

Du könntest aletrnatib auch mit

  
  SELECT MAX(id)  
    FROM tabelle  
   GROUP BY name  
  HAVING count(1) >1  

jeweils eine ID der vorhandenn Dupletten ermitteln und diese dann in der Lösch-Anweisung weiter benutzen.
(Dieses Statement könnte auch mehrmals ausgeführt werden, um auch Drei- oder Mehrfach-Vorkommen zu elimineren)

Ein weiterer Punkt, der in die Überlegungen einfliessen kann, ist ob es mehr Dupletten oder mehr Einzelvorkommen es gibt. Das kann durchaus die Fromulierung eines passenden Statements beeinflussen.

Grüße
  Klaus

[1] Unter Anführungszeichen deshalb, weil dieses 'Feature' in der Regel mehr Kopfzerbrechen bereitet als sinnvoll ist

[Lars]

Hallo Klaus,

Hier stellt sich dann gleich mal die Frage, wie viele Datensätze eigentlich von dem Problem betroffen sind.

Es sind mehr als 400'000 Datensätze betroffen.

Wenn es nämlich eine überschaubare größe ist, dann wäre das manualle erstellen einer Löschanweisung auch abzuwägen.

Manuell wäre zu aufwendig.

Du könntest aletrnatib auch mit

SELECT MAX(id)
    FROM tabelle
   GROUP BY name
  HAVING count(1) >1

>   
  

> jeweils eine ID der vorhandenn Dupletten ermitteln und diese dann in der Lösch-Anweisung weiter benutzen.  
> (Dieses Statement könnte auch mehrmals ausgeführt werden, um auch Drei- oder Mehrfach-Vorkommen zu elimineren)  
  
Habe ich schon einmal versucht. Habe die einzelnen IDs dann in einen String geschrieben und als Löschkriterium genutzt. Folge war wieder ein Speicherproblem. Die gleichen Datensätze sind einmal, zweimal etc. mehrfach vorhanden.  
  

>   
> Ein weiterer Punkt, der in die Überlegungen einfliessen kann, ist ob es mehr Dupletten oder mehr Einzelvorkommen es gibt. Das kann durchaus die Fromulierung eines passenden Statements beeinflussen.  
>   
  
Es sind definitiv mehr Einzelvorkommen als Dupletten in der Tabelle.  
  
Danke vorab  
  
Lars

[André Laugks]

Hallo!

[...]

Versuch mal folgendes:

  
CREATE TABLE adressen_2_tmp SELECT id, Firmenname FROM adressen_2 GROUP BY Firmenname;  
DELETE TABLE adressen_2;  
ALTER TABLE adressen_2_tmp RENAME adressen_2;  

Und dann setze einen UNIQUE-Index, dammit Du keine Dubletten mehr bekommst.

André Laugks

[Klaus Mock]

Hallo,

Du könntest aletrnatib auch mit

SELECT MAX(id)
    FROM tabelle
   GROUP BY name
  HAVING count(1) >1

> > ...  
  

> Habe ich schon einmal versucht. Habe die einzelnen IDs dann in einen String geschrieben und als Löschkriterium genutzt. Folge war wieder ein Speicherproblem. Die gleichen Datensätze sind einmal, zweimal etc. mehrfach vorhanden.  
  
1\.) Wenn Deine Datenbank es kann, dann kannst Du auch mit einem Subselect bei der lösch-Anweisung arbeiten  
~~~sql
  
   DELETE FROM tabelle  
    WHERE id IN ( .. hier das andere Select Statement rein ...)  

Wenn dabei die Datenbank überforder wird dann  weiter mit

2.) Es gibt immer noch die Möglichkeit z.B. mittels Einsatz von LIMIT() die Menge der gefunden Dubletten pro Durchlauf einzuschränken. Das bedeutet zwar mehrere Durchläufe, aber letztendlich sollte es dann doch irgendwann erledigt sein.

Und um das in Zukunft zu vermeiden solltest Du dringen den hier mehrfach gemachten Vorschlag umsetzen, einen UNIQUE-Constraint zu setzen. dann kommen keine Dubletten mehr in die Dtaenbank. Was übrigens auch eine gute Idee ist, das in künftigen Projekt von anfang an gelich einzubauen.

Grüße
  Klaus

[_King Lully]

Du könntest aletrnatib auch mit

SELECT MAX(id)
    FROM tabelle
   GROUP BY name
  HAVING count(1) >1

>   
> jeweils eine ID der vorhandenn Dupletten ermitteln und diese dann in der Lösch-Anweisung weiter benutzen.  
  
Mein Gott, warum nicht "aletrnatib" die "Dupletten" per Mengenlogik einfach elimieren, statt einen wie auch immer gearteten Lauf starten?

[_King Lully]

Danke schon mal vorab,

Mal eine Skizze:

DELETE
FROM
DT_1
WHERE
DT_1_ID NOT IN
(
SELECT DISTINCT
  DT_1_ID               --,
                        --  <Kriterienliste>
FROM
  DT_1
)

> >   
> > Haben jetzt wenig Zeit, scheint mir ziemlich einfach zu sein, aber vielleicht irren wir, bitte prüfen...  
>   
> Habe ich schon mal getestet. Das Problem ist das karthesische Produkt der beiden Abfragen, dieses wird zu groß für den Speicher (von der Ausführungszeit ganz zu schweigen)  
  
Bitte mal präzisieren, wir sehen hier kein Produkt:  
  
"  
<?php  
echo "lese Daten: "; // Kontrollausgabe, ob der Skript überhaupt gestartet ist  
  
$daten = mysql\_db\_query("datenbank","select id, name from tabelle group by name order by name") or die(mysql\_error());  
  
echo "Totalcount einzelne Firmennamen: ".mysql\_num\_rows($daten)."\n\n"; // Kontrollausgabe, um die Datenmenge  zu sehen  
  
while ($wert = mysql\_fetch\_array($daten) )  
{  
    $i++;  
    mysql\_db\_query('datenbank','delete from tabelle where name = "'. addslashes($wert[name]).'" and id != "'.$wert[id].'"') or die (mysql\_error());  
    echo "Name: ". $wert["name"] .", Datensatz: $i "; // erneute Kontrollausgabe, um zu prüfen, ob der Skript überhaupt noch arbeitet  
}  
echo "Fertig"; //Letzte Kontrollausgabe  
?>  
"  
  
Und, bitte mal die Anforderung konkretisieren, zumindest wenn unser vorgeschlagenes Vorgehen unzureichend erscheint.  
  
(Wenn Du überhaupt keine Ahnung hast, auch OK! Aber bitte jetzt kurz darauf hinweisen.)

[Georg]

Hallo Lars,

wenn ich dass Problem richtig verstehe, gibt es eine Tabelle mit einem Feld 'name', das keine doppelten Einträge mehr haben soll.
Sofern das Feld 'name' nicht vom Type 'Text' ist, sollte eigentlich folgendes  reichen:
ALTER IGNORE TABLE tabelle ADD UNIQUE (name)

Du legst also einfach fest, dass name UNIQUE sein soll, also keine zwei Einträge mit dem selben Wert mehr existieren dürfen. Das IGNORE bewirkt, dass alle Einträge, die gegen das UNIQUE Kriterium verstoßen würden rausfallen.

mfg
Georg