✔ Abfragen Optimierung

Steusi

Steusi

Nasenbär
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: 
$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....
SQL: 
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
 
Zuletzt bearbeitet von einem Moderator:
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 :-(

SQL: 
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)
 
Zuletzt bearbeitet von einem Moderator:
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:
SQL: 
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.
 
Zuletzt bearbeitet von einem Moderator:
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
SQL: 
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
 
Zuletzt bearbeitet von einem Moderator:
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.
SQL: 
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;
 
Zuletzt bearbeitet von einem Moderator:
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!
 
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück