Hallo Leute,

ich habe ein Problem, leider schmiert mir der MySQL-Dienst bei der Ausführung des Scripts ab. Ich denke die Abfrage ist zu aufwendig für mein einfaches Vorhaben formuliert.

Ich habe 2 Tabellen, in jeder stehen Paketnummern, welche in beiden Tabellen vorhanden sein sollen. Anhand der Paketnummer errechnet sich ein Gesamtsumme.

In der 1. Tabelle befinden sich 30.000 Datensätzen und in der 2. Tabelle ca. 150.000 (stetig wachsend), welche alle überprüft werden müssen.


Meine 3 relevanten Abfragen sehen wie folgt aus:

PHP-Code:

$select_all = mysql_query("SELECT cost_unit, SUM(amount) AS summe
                           FROM export
                           INNER JOIN mail
                           ON export.packetno = mail.packetno
                           WHERE tax = 19
                           GROUP BY cost_unit");

$select_free = mysql_query("SELECT cost_unit, SUM(amount) AS summe
                           FROM export
                           INNER JOIN mail
                           ON export.packetno = mail.packetno
                           WHERE tax = 0
                           GROUP BY cost_unit");

$select_not_mail = mysql_query("SELECT SUM(amount) AS summe
                                FROM mail
                                WHERE packetno NOT IN
                                    (SELECT packetno
                                    FROM export)
                                "); 


Ich hoffe jemand hat eine Idee, wie man es charmanter und einfacher löschen kann.

Vielen Dank

Bei welcher schwirrt er ab?

ggf mal mit Unterabfragen probieren wo zuerst di kommt, die die Datensäte ruduziert.
Da du keine Laiase oder Tabellennamen bei den Feldern mit angegeben hast, musste ich raten....

Code sql:

1 2 3 4 5 6

SELECT cost_unit, SUM(amount) AS summe FROM     (SELECT packetno FROM mail WHERE tax = 19) AS m     INNER JOIN (SELECT packetno, cost_unit, amount FROM export) AS e     ON e.packetno = m.packetno GROUP BY cost_unit

Ok, natürlich kann man die Abfrage in Subquerys aufteilen, aber bleibt die Last dadurch nicht annähernd gleich hoch?

Also, allein die 1. Abfrage führt dazu, das der MySQL-Deamon 99,9% der CPU einnimmt, was bei der Subquery-Abfrage nicht anders aussieht

Code sql:

1 2 3 4 5 6 7

SELECT cost_unit, SUM(amount) AS summe                       FROM                         (SELECT packetno, amount FROM mail WHERE tax = 19) AS m                         INNER JOIN                         (SELECT packetno, cost_unit FROM export) AS e                         ON e.packetno = m.packetno                       GROUP BY cost_unit

Mit deinem Script, dauert es nur 234 561.5 ms (= 3.9 Min),
statt 282 711.9 ms (= 4.7 Min), also eine deutliche Verbesserung.

Kann man ganze SELECT-Abfragen in eine Variable/Objekt/temporäre Tabelle packen, damit man diese Abfrage nicht erneut ausführen muss?

Schließlich unterscheiden sich meine ersten 2. Abfragen nur von der Steuer (tax)

1) Wie viele verschiedene tax gibt es?
") Hast du mal Indexe auf die Felder gesetzt? Also ein Index auf die Felder packetno, amount und tax. Dito für die andere Tabelle.
3) Wie häufig ändern sich die Daten?

1) Eine Anspielung auf den Umkehrschluss Es gibt nur 2 Steuern 19% und 0%. Leider weiß ich nicht, wie ich dies geschickt nutzen kann.

2) Indexe, gibt es eine Logik um zu wissen, wann es sich bei welchen Spalten empfehlt?
Genügt folgendes aus:

Code sql:

1 2 3 4 5

CREATE INDEX IDX_e_packetno ON export (packetno)   CREATE INDEX IDX_m_packetno ON mail (packetno)

Muss man in den Abfragen, dann mit den Indices arbeiten?

3) Die Daten werden einmal im Monat erneuert. Die Tabelle mail wird geleert, bevor die neuen Daten per Mail importiert werden.
Die export-Tabelle wird um Export-Dateien monatlich erweitert.

Es erfolgen keine weiteren Zugriffe auf die Datenbank, nur für die monatliche Abrechnung.

1) Du musst also alle Daten Auslesen. Also kannst du die Steuer in den GROUP BY nehmen. Auf Wieviel Daetnsätze reduziert sich die Resultatmenge nach dem GROUP BY?

2) Setze ein Index über alle Felder die du brauchst. ggf ist ein FULL INDEX SCAN schneller als ein FULL TABLE SCAN

Code sql:

1 2 3 4 5

CREATE INDEX IDX_e_packetno ON export (packetno, cost_unit);   CREATE INDEX IDX_m_packetno ON mail (packetno, amount, tax);

3) Eine Möglichkeit ist es, dass du das Resultat in eine eigene Tabele schreibst (zuerst TRUNCATE, dann INSERT). Dies jedesmal nachdem die Daten in der Export- und Mail-Tabelle sind.
Je nach Geschäftsfall ist dies eine proktikable Lösung oder nicht - hängt auch von Frage 1) zusammen

1)
Also bei meiner 1. Abfrage (Tax = 19%) ergeben sich 80 Datensätze.
Bei meiner 2. Abfrage (Tax = 0%) würden sich ca. 5 Datensätze ergeben.
Ich kann jedoch nicht beide Abfragen ablaufen lassen, da ich sonst irgendwann ein TimeOut bekomme.

Die einzige Möglichkeit, die mir einfällt, wäre eine Abfrage durchlaufen lassen, Ergebnis temporär speichern. Zu einer nächsten Seite weiterleiten, welche wieder eine Abfrage ausführt, temporär speichert und wieder zu einer weiten Seite weiterleitet...

2)
Nachdem ich alle erforderlichen Spalten als Index deklariert habe, dauert es 2 Sekunden länger, als wenn ich nur packetno als Index in beiden Tabellen deklariere.

3)
Bei der Mail-Tabelle wird TRUNCATE, dann INSERT verwendet. Das Ergebnis, welches nach den Abfragen entsteht wird bereits abgespeichert, damit ein erneuter Zugriff auf die Daten in Sekundenschnelle erfolgen kann.

Oder meinst du, dass das Ergebnis einzelne Select-Abfragen in eine temporäre Tabelle geschrieben werden sollte?

2) Vergessen wir den Index

3) Eine weitere Tabelle mit den Feldern tax, cost_unit, summe. Nach Änderung der Daten in export oder mail wird der Inhalt dieser Tabelle neu erstellt (in Oracle würde man dieses mittels Materialized View durchführen). Dies sind dann etwa die 100 Datensätze die du in 1) beschrieben hast.
Anschliessend kannst du in bei den Abfragen auf diese Tabelle zugreiffen.

Code sql:

1 2 3 4 5 6 7 8 9 10 11 12

TRUNCATE TABLE mv_tax_sum;   INSERT INTO mv_tax_sum (tax, cost_unit, summe) SELECT     tax,     cost_unit,     SUM(amount) AS summe FROM     mail AS m     INNER JOIN export AS e         ON e.packetno = m.packetno GROUP BY cost_unit;

Eine Schöne Idee, dauert zwar knapp 5 Minuten, jedoch verkürzt sich die 2. Abfrage erheblich.
Trotz einer Einsparung von 3 Minuten in den ersten 2 Abfragen, dauert es durch meine 3. Abfrage immer noch zu lange für eine Ausführung über den Browser.
Zudem gehen durch diese mv_tax_sum-Tabelle Daten verloren. Es gibt Kostenstellen, welche sowohl 0% Steuern als auch 19% Steuern aufweisen.

Ich werde versuchen, die MySQL-Abfragen direkt über die Konsole einzugeben, dann sollte die lange Dauer zu keinem Abbruch führen. Den User werde ich mich einer Wartezeit von 10 Minuten belegen, bevor er das Ergebnis sehen kann.

Vielen Dank yaslaw, für die vielen guten Ansätze und neuen Erkenntnisse
Ich berichte, wie es gelaufen ist, wenn ich es mit der Linuxkonsole gelöst haben!

Zitat von Steusi

Zudem gehen durch diese mv_tax_sum-Tabelle Daten verloren. Es gibt Kostenstellen, welche sowohl 0% Steuern als auch 19% Steuern aufweisen.

hab vergessen tax in den GROUP BY zu schreiben....

Sorry fürs Einmischen, aber bei diesen geringen Datenmengen kann das Verhalten nur auf schlechte Indexe zurück zu führen sein oder aber der DB-Server ist von 1990 und hat einfach zu wenig Saft.

Es bringt natürlich Nullkommanichts, wenn für jede Spalte einer tabelle blind und willkürlich ein einspaltiger Index angelegt wird. Es bringt hingegen sehr wohl etwas, zusammen gesetzte Indexe zu verwenden, wenn die Abfrage dies erfordert. Grund hierfür - MySQL verwendet immer nur einen Index einer Tabelle (zu 99%), es nützen also 25 Indexe nichts, wenn sie alle im Bezug auf die Abfrage suboptimal sind.

Sehr hilfreich für weitere Analysen sind für die Community hier folgende Ausgaben:
1. Show Create Table export;
2. Show Create Table mail;
3. paar Angaben zum Server (grob ob das System aktuellen Anforderungen entspricht)

Anschließend kann dir ganz gut geholfen werden. Du wirst dabei jedoch um Analysen mittels EXPLAIN nicht umher kommen.

Grüße BN

@yaslaw: Dann klappt es wunderbar

@bn:
Aber wie ich die Indizes optimal setze weiß ich leider nicht, lese aber nebenbei eine Anleitung zu dem Thema.

1) Create export:

Code sql:

1 2 3 4 5 6 7 8

CREATE TABLE IF NOT EXISTS `export` (   `ID` INT(11) NOT NULL AUTO_INCREMENT,   `packetno` text COLLATE latin1_german1_ci NOT NULL,   `costumer` text COLLATE latin1_german1_ci NOT NULL,   `firm` text COLLATE latin1_german1_ci NOT NULL,   `cost_unit` text COLLATE latin1_german1_ci NOT NULL,   PRIMARY KEY  (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=86430 ;

2) Create mail:

Code sql:

1 2 3 4 5 6 7 8 9

CREATE TABLE IF NOT EXISTS `mail` (   `ID` INT(11) NOT NULL AUTO_INCREMENT,   `packetno` text COLLATE latin1_german1_ci NOT NULL,   `amount` DECIMAL(10,3) NOT NULL,   `tax` tinyint(11) NOT NULL,   `productno` text COLLATE latin1_german1_ci NOT NULL,   `scandate` text COLLATE latin1_german1_ci NOT NULL,   PRIMARY KEY  (`ID`) ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_german1_ci AUTO_INCREMENT=22724 ;

3) Server:
2.6x Linux-Debian 32bit
CPU: 1x 2,6 Ghz
RAM: 1024 MB

Habe den Server eben einmal eine CPU mehr zugewiesen, doch diese wird nicht genutzt.
Hier im Forum habe ich dazu folgende Erklärung gefunden:

Es ist richtig, dass jede Connection zu MySQL Server als ein Thread läuft und daher nicht mehrere CPUs nutzen kann. Abfragen über diese Connection laufen in diesem Thread. Daher sollte immer versucht werden, mit Connection-Pools, also mit mehreren Connections auf MySQL Server zu arbeiten. Nur so können alle Kerne optimal genutzt werden.

Bringt mir bei einer Abfrage also leider gar nichts

CPU ist nicht alles, wichtiger sind RAM und Festplatten-Speed. 1 GB RAM ist nicht gerade ein Schlaraffenland für den DB Server.

Das Design der beiden Tabellen ist unglücklich.

Vermutung:
packetno - alpahnumerische Zeichenkette mit beschränkter Länge (maximale Länge? => VARCHAR(32)?)
productno - alpahnumerische Zeichenkette mit beschränkter Länge (maximale Länge? => VARCHAR(32)?)
scandate - Datum und Zeit (Text ist hier absoluter Quarck besser TIMESTAMP)
cost_unit - alpahnumerische Zeichenkette mit beschränkter Länge (maximale Länge? => VARCHAR(32)?)

Sollte ich mit der Vermutung alphanumerisch falsch liegen und es sind sogar reine Integer, dann ist der Datentyp Integer immer einem Varchar vorzuziehen.

Diese Veränderung halte ich für unbedingt notwendig. Im Anschluss kann man sich Gedanken über gescheite Indexe machen.

Grüße

Festplattengeschwindigkeit ist kein Problem. Maximale Auslastung liegt bei 15.000 kbit/s und das nur beim Backup, durchschnittlich liegt der Wert bei 1000 kbit/s.
Wenn ich mir den belegten RAM ansehe, liegt dieser bei 226 MB und 61MB Overhead-Verbrauch.
Nur die CPU geht bis 99%, sprich 2660 MHz.

packetno und productno sind nur Zahlen, können aber auch mit 0 beginnen, welche nicht verloren gehen darf. Sind auf eine Länge von 15 Zeichen maximiert.
Scandate ist nicht so wichtig, unter Umständen könnte ich es auch raus nehmen!

cost_unit könnte man in einen Int umwandeln, da ich noch keine mit 0 beginnenden Zahlenketten gesehen habe.

Zitat von Steusi

packetno und productno sind nur Zahlen, können aber auch mit 0 beginnen, welche nicht verloren gehen darf. Sind auf eine Länge von 15 Zeichen maximiert.

Sind sie immer 15 Zeichen Lang und werden vorne mit 0en gefüllt oder sind sie unterscheidlich lang?