Massives Performance-Problem bei UPDATE => Alternativen ?

Hallo zusammen,
ich brauche mal Tipps, wie ich das besser hinbekomme bzw. wo mein Fehler
ist. Freue mich über jede Hilfe !

Ich möchte nächtlich zwei Spalten in einer Datenbank aktualisieren. Dazu
habe ich zwei Cronjobs eingerichtet, die letztlich einfach nur zwei UPDATEs
absetzen.
Leider sind Last und Dauer der Statements so hoch, dass das so nicht klappen
wird. Sicherlich liegt der Fehler bei mir, ich muss die UPDATEs anders
machen. Aber ich verstehe schon nicht, warum das so ein Problem ist. Dann
ist es schwer, eine bessere Lösung zu suchen :-=

Also, vereinfacht und newsgroup-kompatibiliert, beide Statements machen im
Prinzip das gleiche, daher führe ich nur eines an. Sinn des Statements ist
es, in der Tabelle my_table die Spalte my_units mit der Anzahl derjenigen
Datensätze zu füllen, die den gielchen Wert in my_base enthalten, also
quasi eine "Partnersuche". Das innere SELECT macht einen "Katalog" der
existierenden Belegungen von my_base samt der Anzahl der Vorkommen, das
äussere UPDATE soll dann diese Anzahl in alle Datensätze übernehmen.

----
UPDATE my_table LEFT JOIN
(
SELECT my_base,count(*) AS my_counter
FROM my_table GROUP BY my_base
) _temp
ON my_table.my_base=_temp.my_base
SET my_table.my_units=my_counter;
----

Die Tabelle my_table definiert u.a. die Spalten
- my_base VARCHAR(255) DEFAULT ''
- my_units INT UNSIGNED DEFAULT 0
es gibt einen Index auf my_base, Tabellentyp ist InnoDB.

Das innere SELECT alleine benötigt nur wenige Minuten. Messungen zeigen ein
lineares skalieren der Ausführungsdauer, ich prognostiziere ca. 20 Stunden
je Update bei 1.000.000 Datensätzen.
Das kann ja wohl nicht sein !
Die Verwendung eines STRAIGHT_JOINs beschleunigt ca. um den Faktor 2, aber
ich bin nicht sicher, ob ich die Voraussetzungen garantieren kann...

Also meine Bitte:
wer nimmt mir die Tomaten von den Augen ?

--
arkascha

Re: Massives Performance-Problem bei UPDATE => Alternativen ?

arkascha wrote:
^^^^^^^^ Vollständiger Realname wäre nett.

> UPDATE my_table LEFT JOIN
> (
> SELECT my_base,count(*) AS my_counter
> FROM my_table GROUP BY my_base
> ) _temp
> ON my_table.my_base=_temp.my_base
> SET my_table.my_units=my_counter;
>
> Das innere SELECT alleine benötigt nur wenige Minuten. Messungen zeigen ein
> lineares skalieren der Ausführungsdauer, ich prognostiziere ca. 20 Stunden
> je Update bei 1.000.000 Datensätzen.

Ich denke mal, daß das Hauptproblem im JOIN liegt. Um das mal klar zu
machen: Die DB muß jetzt zu jedem Eintrag in der linken Tabelle prüfen,
ob es einen Eintrag in der rechten Tabelle gibt und eine entsprechende
Tabelle im RAM pflegen (und ggfs. auf die Festplatte auslagern).
Anschließend verwirft sie den ganz überwiegenden Teil der Tabelle
vollkommen ungenutzt. Daher auch der massive Anstieg der
Bearbeitungszeit mit wachsender Anzahl Zeilen.

Ohne es getestet zu haben, könnte ich mir vorstellen, daß MySQL besser
optimiert, wenn du das SELECT in die SET Klausel packst. Also etwa so:

UPDATE my_table SET my_units = (SELECT COUNT(*) FROM ...)

HTH,
Dominik
--
Wo kämen wir denn da hin, wenn jeder nur fragte "Wo kämen wir denn
da hin?", aber niemand ginge, um zu sehen, wohin wir kämen, wenn wir
gingen?
(Autor unbekannt)