✔ Geschwindigkeitsoptimierung einer Löschanfrage (MySQL 5)

[fenriz]

Moin,

für ein Update der Tabelle t1 müssen aus dieser alle Tupel mit der MSN, die auch in der Tabelle t2 vorkommen, gelöscht werden. Danach wird der Inhalt von t2 nach t1 übertragen. Das Problem ist die Geschwindigkeit der Löschanfrage. Da ich diesen Vorgang hintereinander auf mehrere Tabellen anwenden muss, wird die Angelegenheit schnell zum Geduldsspiel.

Unter der jeweiligen Anfrage steht die Dauer der Verarbeitung. Wie man sieht, geschieht die Abarbeitung des ersten Statements augenblicklich. Da ich die Tabellenupdates in Zukunft per Skript erledigen will, muss ich die MSN´s per Subquery ermitteln.

1.) DELETE FROM t1 WHERE MSN IN ('2','3','4');
-- Statistics (TIME: 00:00:00, Records: 0, Fields: 0)

2.) DELETE FROM t1 WHERE MSN IN (SELECT DISTINCT field2 FROM t2);
-- Statistics (TIME: 00:03:36, Records: 0, Fields: 0)

3.) DELETE FROM t1 USING t1, t2 WHERE t1.MSN=t2.field2;
-- Statistics (TIME: 00:01:36, Records: 0, Fields: 0)

Die Frage ist, wie bekommt man dies mit einer Subquery Anweisung schneller hin.
Andererseits interessiert mich auch, woher der Zeitunterschied zwischen 1. und 2. kommt. Falls mir jemand die interne Verarbeitung dieser beiden Querys seitens MySQL erklären konnte, wäre auch das sehr hilfreich.

Vielen Dank schon mal im voraus

 

[ManicMarble]

Der Unterschied zwischen 1.) und 2.) ist gravierend: Während 1.) eine einfache Abfrage über eine Tabelle ist, wid in 2.) für jeden Datensatz in `t1` ein Select über `t2` durchgeführt, um die Bedingung zu prüfen.

Für Deine Zwecke ist wohl 3.) der richtige Ansatz, also ein Join, ich würde hier allerdings einen LEFT JOIN in Zusammenarbeit mit einem Subquery empfehlen:

DELETE FROM
  `t1`
LEFT JOIN ( SELECT `field2` FROM `t2` GROUP BY `field2` )
            AS `subquery` ON `subquery`.`field2` = `t1`.`msn`
WHERE
  `subquery`.`field2` IS NOT NULL

 

[fenriz]

Mit MySQL funktioniert die Löschanweisung mit einem LEFT JOIN offenbar nicht. Habs jetzt wie folgt gemacht. Die Verarbeitungsdauer liegt unter einer Sekunde.

DELETE FROM 
	vpmstatus_bcrd2
USING 
	vpmstatus_bcrd2, (SELECT DISTINCT field2 FROM incomevpm) AS subquery
WHERE vpmstatus_bcrd2.msn=subquery.field2;

Vielen Dank für Deine Hilfe

 

[ManicMarble]

Also LEFT JOIN funktioniert prinzipiell schon mit MySQL, natürlich auch mit Subqueries, der Fehler muss wo anders liegen. Deine Abfrage ist im Prinzip das selbe, zumindest nach dem der MySql-Optimizer das in der Mangel hatte. Ich persönlich bin halt ein Kontroll-Freak und bestimme lieber selbst, wie ein Statement bearbeitet wird (Angabe des JOIN-Typs, Verwendung der ON-Syntax, GROUP BY statt DISTINCT...)

Na, aber wenn alles zur Zufriendenheit funktioniert gibt's keinen Grund dran rumzuschrauben...

 

[fenriz]

Moin ManicMarble,

hast Recht, DELETE mit LEFT JOIN funktioniert doch. Es lag an meinem DB Client, der, wenn nach FROM ein Leerzeichen stand, die Abfrage nicht ausführen wollte.

Danke nochmal für Dein Engagement.

Gruß Fenriz