Update dauert ewig

[gmw]

Hi,

ich will in einer Tabelle TABA die Anzahl der Einträge aus TABB speichern, die dort für eine bestimmte Kategorie vorhanden sind. Das habe ich versucht so zu lösen. Funktioniert zwar grundsätzlich auch, aber der Update dauert bei mehreren tausend Einträgen minutenlang. Kann man da noch irgend etwas von der Performance optimieren?

SQL> select * from taba;
        ID     ANZAHL
---------- ----------
         1          0
         2          0
         3          0

SQL> select * from tabb;
        ID     KAT_NR
---------- ----------
         1          1
         2          2
         3          3
         4          1
         5          2
         6          1
         7          1
         8          2
         9          1


SQL> UPDATE taba
SET anzahl = (
  SELECT COUNT(*)
  FROM tabb
  WHERE tabb.kat_nr = taba.id
);
3 rows updated.


SQL> select * from taba;

        ID     ANZAHL
---------- ----------
         1          5
         2          3
         3          1

[ghostgambler]

Du könntest probieren da einen JOIN draus zu machen ... ich weiß aber gerade nicht, ob das von der Syntax her überhaupt geht...

Ansonsten bleibt als weitere Variante das per php aufzubohren, will heißen

$result = mysql_query("SELECT * FROM taba ");
while ($row = mysql_fetch_assoc($result)) {
  $res = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) FROM 
tabb WHERE kat_nr = " . $row['id']));
  mysql_query("UPDATE taba SET anzahl = " . $res['COUNT(*)'] . " 
WHERE id = " . $row['id']);
} 

das sieht zwar auf den ersten Blick komplizierter aus (und ist es wohl auch ~ mal ganz abgesehen davon, dass es mich jedes Mal erneut graut, wenn ich sowas in php machen muss, weil das DBMS zu scheiße ist...), aber es hat den Vorteil, dass die Tabellen nicht ständig gelockt sind und demnach auch SELECTs noch durch gehen...
Ein Update-Statement, was 2 Stunden lang dauert, lockt nämlich 2 Stunden lang beide Tabellen, was man im Live-Betrieb halt schlichtweg nicht reißen kann~

Weitere Variante wäre beide Tabellen zu kopieren (create temporary table), dann das UPDATE darauf fahren (keine Belastung durch den Live-Betrieb) und hinterher die berechneten Anzahlen aus tabb_temp zurück in tabb schreiben - so konnte ich mal riesige DELETE-Joins, welche die Mitglieder-Tabelle lahm gelegt haben, davon runter nehmen...
So oder so, ich persönlich finde alle Lösungen "dreckig"... InnoDB mit row-level-locking könntest du probieren, aber das kam bei uns damals nicht in Frage~

[gmw]

Das PHP Beispiel, das du hier nennst ist genau die Methode, wie ich es im Moment durchführe. Dauert damit knapp unter einer halben Sekunde. Nur dachte ich, ich könnte noch ein paar ms rausholen, wenn ich ohne den Umweg PHP alles in der DB ablaufen lassen (außerdem möchte ich mein DB Wissen doch gerne etwas erweitern). Genau wegen diesem Gedanken hatte ich gehofft, da gäbe es noch einen super Trick, den ich bisher übersehen habe. Temporäre Tabellen finde ich für meine Belange hier noch etwas übertrieben, da es ja im Moment durchaus mit akzeptabler Geschwindigkeit läuft - nur eben nach meinem Empfinden nicht mit besonders Guter.

[ghostgambler]

Sry, abgesehen von JOIN, die MySQL für gewöhnlich schneller abarbeitet, hab ich keine andere Idee~
Aber wenn du den Trick findest, der den DB-Server auch bei Milliarden an Datensätzen pro Tabelle schnell arbeiten lässt, teile ihn mit uns

[gmw]

Ich nenne es mal Designfehler der DB. Habe gerade einen Index auf KAT_NR gelegt (ID war schon der Primary Key) und schon flutscht es. Habe mal die Zeiten verglichen, mit der PHP Lösung benötigt er für meinen Datenbestand ca. 0,3 Sekunden, mit der reinen DB Abfrage rund 0,03 Sekunden. Also um den Faktor 10 schneller, genau was ich wollte. Super...

Der Trick, um die DB schneller arbeiten zu lassen heißt natürlich Hardware. Sehr viel mehr sehr viel schnellere Hardware, dann wird auch die DB schneller.

[ghostgambler]

Original geschrieben von gmw
Ich nenne es mal Designfehler der DB. Habe gerade einen Index auf KAT_NR gelegt (ID war schon der Primary Key) und schon flutscht es. Habe mal die Zeiten verglichen, mit der PHP Lösung benötigt er für meinen Datenbestand ca. 0,3 Sekunden, mit der reinen DB Abfrage rund 0,03 Sekunden. Also um den Faktor 10 schneller, genau was ich wollte. Super...

Wirf mal mehr Datensätze in die Tabellen~

Du hast einen correlated subquery als Update, die sind für gewöhnlich tödlich...