Update Performance Problem

[tompez]

Hallo liebe Helfer,

ich arbeite zur Zeit an einem Programm, welches mit Hilfe von polyphonen Motiven Musik komponieren soll. Die relevanten Daten speichere ich in einer MySQL-DB lokal auf meinem Rechner (localhost). Nachdem alle relevanten Daten in der DB sind, möchte ich Bewertungen von Takten vornehmen, ich dachte am Server geht das recht schnell. Leider braucht das Update-Statement mehrere Minuten.

Kann mir jemand vielleicht bei der Optimierung helfen? Gibt es einen Weg folgendes schneller zu bewältigen?

Ein Bild vom ERD (einem Teil):



Beschreibung:
-ein Motiv (polymotif) gehört zu einer Kategorie
-ein Motiv besteht aus 1:n Takten (polybeat)
-ein Takt besteht aus 1:n Noten (polynote)
-die Tabelle note_amount ist unabhängig und beinhaltet Noten mit ihrem Prozentanteil (amount) innerhalb der Kategorie (zur Bewertung der Interessantheit)

Anmerkung:
-eine Note (polynote) existiert nur 1 Mal in der DB. zB.: ein C der 3. Oktave mit der Dauer 0.5 existert maximal 1 Mal und es wird immer darauf referenziert, sie kann aber als C der 3. Oktave mit einer Dauer von 1.0 auch noch existieren, etc.
-bei allen anderen Tabellen passiert keine Überprüfung, ob es möglich wäre zu referenzieren (kein existcheck)

hier noch das dazugehörige Statement:
aktualisiere das Interessantheitsmaß aller Takte (polybeat), welche zur Kategorie 1 (pm.category_id) gehören und Noten beinhalten, welche einen Anteil zwischen 5 und 10 Prozent besitzen.

Anmerkung: das SELECT-statement (temp) musste ich noch dazu machen, da sonst die Tabelle polybeat nicht in der FROM-Clause des Update-Statements enthalten sein durfte.

UPDATE pbmc.polybeat pbb SET pbb.interesting_measure = 1 WHERE pbb.id IN
    (SELECT temp.ids FROM
        (SELECT DISTINCT(pb.id) AS ids FROM pbmc.polymotif pm
            JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id
            JOIN pbmc.polybeat pb ON pb.id=bim.beat_id
            JOIN pbmc.noteinbeat nib ON nib.polybeat_id=pb.id
            JOIN pbmc.polynote pn ON pn.id=nib.polynote_id
            WHERE pm.category_id = 1 AND pn.pitch IN
                (SELECT na.pitch
                FROM pbmc.note_amount na
                WHERE na.category_id = pm.category_id AND na.amount BETWEEN 5 AND 10)) AS temp)

und wenn wir schon dabei sind und ihr euch die Mühe macht, ich hätte auch noch ein zweites langsames Statement:
aktualisiere das Tempo aller Takte (polybeat), welche zur Kategorie 1 (pm.category_id) gehören und deren Notenanzahl zwischen 7 und 12 liegt.

UPDATE pbmc.polybeat pb SET pb.tempo = 1 WHERE pb.id IN
    (SELECT IDS.beat FROM
        (SELECT pm.id AS motif, pb.id AS beat, Count(pb.id) AS Notecount FROM pbmc.polymotif pm
        JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id
        JOIN pbmc.polybeat pb ON pb.id=bim.beat_id
        JOIN pbmc.noteinbeat nib ON nib.polybeat_id=pb.id
        JOIN pbmc.polynote pn ON pn.id=nib.polynote_id
        WHERE pm.category_id = 1
        GROUP BY pm.id, pb.id HAVING Notecount BETWEEN 7 AND 12) AS IDS)

herzlichen Dank schon mal an all jene, die sich die Mühe machen alles durchzulesen und sich Gedanken machen.

 

[tompez]

Noch ein paar Infos:

Anzahl der Zeilen aller gejointen Tabellen: 53507 (Dauer des Count(*)-Statements: 53.961 sec)

Tabellen joinen und nach einer Kategorie filtern - Duration/Fetch: 0.344 sec / 0.795 sec

SELECT * FROM pbmc.polymotif pm
    JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id
    JOIN pbmc.polybeat pb ON pb.id=bim.beat_id
    JOIN pbmc.noteinbeat nib ON nib.polybeat_id=pb.id
    JOIN pbmc.polynote pn ON pn.id=nib.polynote_id
    WHERE pm.category_id = 1;

schon alleine durch das Count(*) erhöht sich die Dauer der Ausführung von 1 Sekunde auf 54 Sekunden ....

 

[akretschmer]

Noch ein paar Infos:

Anzahl der Zeilen aller gejointen Tabellen: 53507 (Dauer des Count(*)-Statements: 53.961 sec)

Tabellen joinen und nach einer Kategorie filtern - Duration/Fetch: 0.344 sec / 0.795 sec

SELECT * FROM pbmc.polymotif pm
    JOIN pbmc.beatinmotif bim ON pm.id=bim.motif_id
    JOIN pbmc.polybeat pb ON pb.id=bim.beat_id
    JOIN pbmc.noteinbeat nib ON nib.polybeat_id=pb.id
    JOIN pbmc.polynote pn ON pn.id=nib.polynote_id
    WHERE pm.category_id = 1;

schon alleine durch das Count(*) erhöht sich die Dauer der Ausführung von 1 Sekunde auf 54 Sekunden ....

Passende Indexe? Was sagt EXPLAIN?

IIRC verarbeitet MySQL Dinge wie foo in (select ...) alles andere als schnell. Auch mit vielen JOINS hat es so seine Probleme. Wäre ich Du, würde ich es mal unter PostgreSQL versuchen.
Spaßeshalber könntest Du mir einen Dump aller Tabellen schicken und ich werf es mal in PG ein. Ich bin mir sehr sicher, daß es da deutlich schneller gehen würde.

 

[tompez]

Hallo,

danke für die Tipps. Ja, Indices sollten passen. Auf eine andere DB kann ich leider nicht umsteigen, da die DB schon existiert und sie auch weiterhin genutzt wird. Mir bleibt leider keine Wahl.

Ich habs jetzt aber so halbwegs hinbekommen. Mache es nun mit einer temporären Tabelle, damit nicht bei jedem Update alles neu ausgeführt wird. Dauert zwar gut 1 Minute für die Ausführung, aber das ist verkraftbar, da das Statement nur sehr sehr selten ausgeführt wird.

Danke auf jeden Fall für die Hilfe.