Flaschenhals bei Query finden (mysql(i))

[Rolf B]

Hallo Jörg,

danke für die Rückmeldung. Damit kann ich arbeiten 😂

(1) SQL_CALC_FOUND_ROWS

Wie verlinkt - das Ding ist langsam. Es wird gesagt, dass es günstiger sei, die Query ohne SQL_CALC_FOUND_ROWS durchzuführen, und die Anzahl der Zeilen mit einem SELECT COUNT(*) separat zu ermitteln. Weil der Server dann COUNT-Optimierungen nutzen könnte. Das ist in deinem Fall besonders deutlich, weil bei Dir die Anzahl der Ergebniszeilen durch die Anzahl der gefundenen Rechnungsnummern mit Belegart 'RG' bestimmt wird. Du kannst an Stelle des separaten SELECT FOUND_ROWS(), mit dem Du das Ergebnis des SQL_CALC_FOUND_ROWS abholen müsstest, alle JOINS und Subselects weglassen und einfach

SELECT COUNT(*) FROM rechnungen WHERE belegart = 'RG'

machen. Das sollte in Summe schneller sein.

Wenn Du eine LIMIT Verarbeitung machst, weil Du das Ergebnis durchblättern willst, könnte es auch helfen (weiß ich aber nicht - MYSQL optimiert LIMIT Angaben ziemlich gut), wenn Du in einer ersten Query die zu zeigenden RechnungsIDs bestimmst. Da es zu einer Rechnungsnummer immer nur einen Satz gibt, sollte sich das allein mit der Rechnungen-Tabelle ausführen lassen. Für die Monsterquery fügst Du in der WHERE-Klausel noch ein "AND RechnungenID IN ($idlist)" hinzu. $idlist ist eine kommaseparierte Implosion der IDs aus der ersten Query. Du solltest aber im PHPMYADMIN erstmal manuell versuchen, ob das überhaupt merklich was bringt. Versuche unterschiedliche ID-Bereiche einmal mittels LIMIT und einmal mittels IN Auflistung zu selektieren und vergleiche die Laufzeit.

(2) GROUP BY

Ein Beispiel - bei Dir ggf. so nicht relevant. Es soll das GROUP BY Thema klarstellen

Tabelle 1 (Rechnungen)

RechnungID  Nummer  Betrag
         1      17   12,10
         2      37   13,25
         3      97   22,50

Tabelle 2 (Berechnungen)

BelegID       Datum  Typ  Ergebnis
      1  01.01.2021   XY      1234
      1  11.01.2021   XZ      4711
      2  05.01.2022   QI       815

Nun machst Du

SELECT Nummer, Betrag, Datum, Ergebnis
FROM rechnungen r 
    LEFT JOIN berechnungen b ON r.RechnungID = b.BelegID
GROUP BY Nummer

Die temporäre Ergebnistabelle vor dem GROUP BY sieht so aus

Nummer  Betrag      Datum  Ergebnis
  17     12,10  01.01.2021     1234
  17     12,10  11.01.2021     4711
  37     13,25  05.01.2022      815
  97     22,50        NULL     NULL

Und dein SQL Server steht nun beim Gruppieren vor einer Entscheidung, die er nicht treffen kann. Welchen Betrag, welches Datum und welches Ergebnis soll er für Nummer 17 wählen? Das ist durch die Query nicht festgelegt. Deswegen ist das bei den meisten SQL Systemen ein Fehler. Du musst eine Aggregierungsfunktion verwenden (COUNT, MIN, MAX, SUM, AVG). Beim Betrag ist die Entscheidung treffbar, aber SQL lässt sich auf diesen Sonderfall gar nicht ein. Was im GROUP BY steht, wird nicht aggregiert und was nicht im GROUP BY steht, muss aggregiert werden.

MYSQL ignoriert dieses Entscheidungsproblem, indem es einfach den ersten Satz der Gruppe verwendet.

(3) GROUP BY - nochmal

Bei Dir ist es so, sagst Du, dass die Rechnungentabelle zu jeder Nummer genau einen Satz enthält und eine Mehrdeutigkeit nur durch die Joins entstehen kann. Gucken wir uns das im Licht des Explains nochmal an.

Der Explain sagt eigentlich überall "Rows=1" - außer bei der Datev und bei den Berechnungen. Die Mehrdeutigkeit kommt also nur von dort.

Du schreibst, es könnte mehrere Zahlungseingänge zu einer Rechnung geben. Was hat es denn dann mit der Spalte ze.bezahlt auf sich? Hier kommt wieder der GROUP BY Konflikt ins Spiel. Wenn es 3 Zahlungseingänge gibt und nicht alle den gleichen Wert für "bezahlt" haben - welcher dieser Werte gehört dann ins Ergebnis? Was sagt "bezahlt" bei den Zahlungseingängen überhaupt aus?

Bei der Datev kann man es mit einem Subselect lösen. Schreibe statt da.ListenID einfach dies in die Spaltenliste des SELECT - ähnlich wie bei den Bruttozahlungen:

(SELECT ListenID FROM daten_belege 
                 WHERE belegID=r.rechnungenID LIMIT 1) AS ListenID,

Dieses AS ListenID hintendran gibt der berechneten Spalte einen Namen. Machst Du bei der Bruttosumme ja auch. Das solltest Du auch bei den berechneten Timestamps machen, PHP Code ist besser wartbar, wenn er mit Spaltennamen statt Spaltennummern arbeitet.

Was es mit den Berechnungen auf sich hat - tja. War ja offenbar ein unnötiger JOIN 😂, den zu streichen ist die beste Optimierung.

Wenn Du nur noch Einzelsätze hinzujoinst, sollte sich der GROUP BY erledigt haben. Dafür muss allerdings zuerst geklärt werden, wie mit der Spalte ze.bezahlt bei mehreren Zahlungseingängen umzugehen ist.

Die Eindeutigkeit kannst Du dann im phpMyAdmin validieren, indem Du zwischen GROUP BY Klausel und ORDER BY Klausel ein

HAVING COUNT(*) > 1

einfügst. Natürlich nicht in der PHP Anwendung. Die HAVING-Klausel ist sozusagen ein zusätzliches WHERE, das nach dem GROUP BY ausgeführt wird. Der Gezeigte HAVING liefert Dir alle Rechnungsnummern, für die es vor dem GROUP BY mehr als einen Satz gab.

Rolf