MySql5: DELETE Abfrage optimiereren

Headhunter

Hallo zusammen,

ich möchte folgende Abfrage optimieren:

DELETE 
FROM  TabelleA
WHERE WertA NOT IN (
   SELECT WertB
   FROM   TabelleB
   WHERE  WertB = WertA )

Soweit so gut.. in TabelleA sollen alle Zeilen entfernt werden, die in TabelleB nicht auftauchen.
Das Problem dabei ist, dass beide Tabellen gut und gerne mehrere Millionen Datensätze speichern und die Abfrage wirklich *ewig* braucht um fertig zu werden. Schon bei einigen 1000 Rows braucht's schon mehr als zehn Sekunden, bei mehr wird es unterträglich lahm.

Wie gehts flotter? Ein LIMIT kann MySql leider nicht in DELETE Abfragen. Drecksdbms. Danke für eure Hilfe

Edit: MySql Version ist die Nr. 5

Unix-Tom

Hast du LIMIT schon vesucht?
Klar geht LIMIT in DELETE.

Headhunter

Moin,

ein LIMIT funzt bei mir nicht.. ich habe im Moment meinen SQL Server nicht zur Verfügung, aber folgendes Statement gibt mir einen Syntaxfehler:

DELETE 
FROM  TabelleA
WHERE WertA NOT IN (
   SELECT WertB
   FROM   TabelleB
   WHERE  WertB = WertA 
   LIMIT  1)

*grimmsen**

Von welchem Typ sind WertA und WertB? Und sind die Werte indiziert?

Gehe mal in eine Admin-Console von mysql, und führe:

FLUSH STATUS;

aus. Jetzt mache die langsame Query, und schau ob MySQL temporäre Tabellen auf der Festplatte erzeugt oder nicht:

SHOW STATUS WHERE Variable_name LIKE "created_tmp%";

Wenn die tmp_tables auf Platte erzeugt wurden, solltes du tmp_table_size hochsetzen, wie gross ist dieser Wert?

SHOW variables WHERE Variable_name='tmp_table_size';

Wenn einfach nur das DELETE langsam ist kannst du

DELETE QUICK ...

nutzen, solltest aber öfter die Tabelle aufräumen (OPTIMIZE TABLE ...; oder myisamchk).

MySQL ist übrigens garnicht so schlecht, wie du meinst!

Headhunter

Hello,

danke für deine Antwort,

WertA und WertB sind beides jeweils die Primary Keys des Tabellen, Datentyp ist jeweils INTEGER.

Die Geschichte mit dem Temptables werde ich Zuhause später mal untersuchen, im Moment gehts halt nicht.
Aber wie auch immer - ich erwarte von einem richtigem DB System, dass solch eine Abfrage in 0,nix abgearbeitet wird. Lächerlich dass das nicht funzt. Wenn die Frage beantwortet ist können wir gerne darüber diskutieren ob MySql scheiße ist oder nicht.. ich lasse deine Aussage also mal einfach unkommentiert im Raume stehen.

Unix-Tom

Du kannst von einem Select nicht erwarten das ein abfrage Zeit 0 braucht wenn Daten vorhanden sind.
Es gibt keine Db welche das kann.
Selbst wenn du keine SELECT in DELETE hast aber dafür WHERE muss die DB die Tabelle durchsuchen.
Ich habe übrigens eine Tabelle mit 500000 Datensätzen mit 10 Splaten die 300MB groß ist. Eine Abfrage bzw. DELETE update dauert da nur millisekunden. Ich erreiche das indem ich index auf mehrere Spalten habe.

Headhunter

Hallo,

meine Tabellen haben 5 bzw. 3 Spalten, wobei jeweils der zu suchende Befehl der PKey ist.
Die Abfrage kann ja ruhig etwas Zeit in Anspruch nehmen, so oft wird die eh nicht ausgeführt. Aber solange wie ich es beobachte ist es schlicht und einfach inakzeptabel.

Nur um dir zu zeigen wie scheiße die Perfomance ist, hier ist die (momentan) schnellste Version um meine Aufgabe zu erledigen (PHP Code, aus dem Kopf)

do
{
  $high = mysql_result(mysql_query('SELECT TOP(WertB) FROM TabelleB'));
  mysql_query ("DELETE FROM TabelleA WHERE WertA=$high";

} while (mysql_num_affected_rows());

Nochmal: Dieser Code ist schneller als die zuerst gepostete Abfrage!

Ansonsten ist die Performance von MySql durchaus OK. Auch komplexere Abfragen über größere Datenbestände laufen akzeptabel schnell..

Hm... Ich weiß nicht so genau, aber ist die WHERE-Bedingung in dem Sub-Select überhaupt nötig? M.E. kann man die getrost weglassen. Vielleicht ist das ja schon die Bremse.

tntnet

Headhunter schrieb:

DELETE 
FROM  TabelleA
WHERE WertA NOT IN (
   SELECT WertB
   FROM   TabelleB
   WHERE  WertB = WertA )

ist gleichbedeutend mit:

DELETE
  FROM TabelleA
 WHERE WertA NOT IN (
    SELECT WertB
      FROM TabelleB)

Das ist Standard-SQL. Nichts Mysql-spezifisches. Und das sollte allemal schneller sein, als irgendeine selbstgeschriebene Schleife.

@tntnet: Und wo ist da jetzt der Unterschied zu meinem Post?

Headhunter

Hallo,

die Wherebedingung habe ich eingebaut, weil ich dachte so die Anzahl der Ergebniszeilen einschränken zu können. Ich werde am WE nochmal schauen was mit der Abfrage los ist..

Hallo

für die Fälle, in denen gelöscht werden soll schränkt deine Where Abfrage ja aber gar nichts ein. In dem Fall erzeugt sie nur eine erneute überprüfung, ob der WertA nicht in TabelleB ist und das wird dann durch jeweils neue WerteA immer wieder gemacht.
Es sind also immer wieder neue selects durchzuführen.

Mal so ganz locker aus der Haose geschossen. Kann natürlich auch sein, dass das gleich wegoptimiert wird und das Problem wo anders liegt.

Jens

Headhunter

Ja, das ist noch so etwas was bei MySql so Klasse ist!
Was macht SELECT * FROM X INNER JOIN ON a=b GROUP BY c denn nun im genau?
Keine Ahnung, wer braucht schon nen EXPLAIN Befehl oder ähnliches für seine Statements? Das wird schon reichen für das nächste PHP Gästebuch..
Oh.. Offtopic, egal..

Shade Of Mine

Headhunter schrieb:

Ja, das ist noch so etwas was bei MySql so Klasse ist!
Was macht SELECT * FROM X INNER JOIN ON a=b GROUP BY c denn nun im genau?
Keine Ahnung, wer braucht schon nen EXPLAIN Befehl oder ähnliches für seine Statements? Das wird schon reichen für das nächste PHP Gästebuch..
Oh.. Offtopic, egal..

ach komm, troll woanders, ok?

ist ja nicht so als waere mysql das einzige dbms wo es mal performance probleme bei nem query gibt.

und n explain gibt es