MySQL Lösung JOIN/Subselect <==> PHP Version

[WernerK]

Hallo liebe Helfer,
Immer wieder liest man hier im Forum, dass man besser direkte MySQL Abfragen machen soll anstatt es über PHP zu lösen.
Ich möchte mal gerne eure Meinung wissen zu folgender Situation.
Es gibt eine etwas umfangreichere MySQL Abfrage:

  
SELECT B.Kunde, A.IdKd,A.beschreibung, A.maschserien AS SerienNummer  
From  
software A  
LEFT JOIN bestellung B ON B.IdKd = A.IdKd  
WHERE SUBSTRING(A.maschserien, -6) IN  
(SELECT SUBSTRING(X.maschserien, -6)  
From  
software X  
WHERE X.maschserien != ""  
GROUP BY SUBSTRING(X.maschserien, -6)  
HAVING Count(*) > 1  
ORDER BY X.IdKd ASC)  
ORDER BY SUBSTRING(A.maschserien, -6) ASC, A.IdKd ASC  

Diese Abfrage in PhpMyAdmin abgesetzt dauert sage und schreibe 8 Sekunden und liefert mir 285 Datensätze

Das Ganze nun mit PHP umsetzt in zwei Abfragen und mit While Schleifen:

  
$result = mysql_query("  
SELECT  
SUBSTRING(X.maschserien, -6) AS maschcode  
From software X  
WHERE  
X.maschserien != ''  
GROUP BY  
SUBSTRING(X.maschserien, -6)  
HAVING Count(*) > 1  
ORDER BY X.IdKd ASC  
");  
while($var = mysql_fetch_array($result)){  
$result1 = mysql_query("  
SELECT B.Kunde, A.IdKd,A.beschreibung, SUBSTRING(A.maschserien, -6) AS SerienNummer  
From  
software A  
LEFT JOIN bestellung B ON B.IdKd = A.IdKd  
WHERE SUBSTRING(A.maschserien, -6) = '$var[maschcode]'  
ORDER BY SUBSTRING(A.maschserien, -6) ASC, A.IdKd ASC  
");  
while($var1 = mysql_fetch_array($result1)){  
echo "Ausgabe..." ... ";  
}  

Die PHP Lösung liefert mir genau die gleichen Datensätze (natürlich in einer anderen sortierten Ausgabe) aber wesentlich schneller. (ich würde sagen im Millisekunden Bereich)

Die Frage ist nun. Was ist sauberer, besser?

viele Grüsse
Werner

[Vinzenz Mai]

Hallo,

Immer wieder liest man hier im Forum, dass man besser direkte MySQL Abfragen machen soll anstatt es über PHP zu lösen.

in aller Regel ist dies der bessere Weg.

SELECT B.Kunde, A.IdKd,A.beschreibung, A.maschserien AS SerienNummer
From
software A
LEFT JOIN bestellung B ON B.IdKd = A.IdKd
WHERE SUBSTRING(A.maschserien, -6) IN
(SELECT SUBSTRING(X.maschserien, -6)

-- sowas ist übel, sehr übel.
-- nicht indizierbar, eine Performancebremse erster Güte

From
software X
WHERE X.maschserien != ""
GROUP BY SUBSTRING(X.maschserien, -6)
HAVING Count(*) > 1
ORDER BY X.IdKd ASC)
ORDER BY SUBSTRING(A.maschserien, -6) ASC, A.IdKd ASC

  
Es ist kein Wunder, dass diese Abfrage lahm ist.  
  

> Die Frage ist nun. Was ist sauberer, besser?  
  
Die einzig vernünftige Maßnahme wäre, das Tabellendesign zu reparieren. Wenn die letzten 6 Zeichen einer Zeichenkette eine Seriennummer sind, so ist es unabdingbar, dass diese in einer eigenen Spalte (mit einem Index) geführt werden. Der Inhalt der derzeitigen Spalte läßt sich ganz sicher über eine einfache Operation, z.B. in einem View, der den Namen der derzeitigen Tabelle erhalten wird, darstellen. Löse dieses Problem und Du löst die Performanceprobleme.  
  
  
Freundliche Grüße  
  
Vinzenz

[Wernerk]

Hallo Vinzenz, Hallo Sven,

erst mal herzlichen Dank an euch Experten für die Erklärungen.

-- sowas ist übel, sehr übel.
-- nicht indizierbar, eine Performancebremse erster Güte

Ja das habe ich auch daraus gelernt. Das Problem war / ist, dass das Design ursprünglich mal so aufgebaut wurde. Eigentlich war das Ganze nur mal so zum testen gedacht. Nun wird es richtig eingesetzt. Ich habe schon eine zusätzliche Spalte eingefügt wo ich dann den String Anteil z.b. "XHAC" von XHAC244888 speichere und die eigentliche Seriennummer als Zahl 244888 weiterhin in der Spalte "maschserien". Ich müsste nur noch alle vorhandenen Zeilen bzw. Datensätze umkopieren.

vielen Dank nochmals

Gruss
werner

[Sven Rautenberg]

Moin!

Immer wieder liest man hier im Forum, dass man besser direkte MySQL Abfragen machen soll anstatt es über PHP zu lösen.

Das ist im Grundsatz erstmal richtig, denn das ist die sauberere Lösung, die keine Vorab-Optimierung vornimmt.

Nun hast du allerdings jetzt eine konkrete SQL-Abfrage, deren Performance zu wünschen übrig läßt. Deinem Query sieht man als informierter Interessent an, dass sie deshalb langsam wird, weil nahezu keinerlei Indexoperationen genutzt werden können. Das liegt vor allem an der intensiven Nutzung der Funktion SUBSTRING() und dem Subselect. MySQL muss hier also richtig viel arbeiten, mit Full-Table-Scans etc.

Der Ausweg aus so einer Situation besteht in der Tat aus zwei Alternativen: Entweder man optimiert die Datenbank (indem bessere Indices gesetzt werden, evtl. redundante Felder erzeugt werden, die vorberechnet das Funktionsergebnis enthalten, und der Query optimiert wird durch Untersuchung des Ausführungsplans mittels EXPLAIN), oder man splittet die Querys an exakt der Stelle auf, die bei der Abfrage zu umfangreichen Kreuztabellenoperationen führt, und gewinnt durch Aufspaltung der Abfrage in eine erste, sehr stark vorfilternde Abfrage mit relativ wenig Datensätzen plus eine zweite Abfrage oder Abfragenserie, die diese wenigen Datensätze indexbasiert schnell auslesen kann, mehr Zeit, als man mit einer "sauberen", den Lehrmethoden entsprechenden Query verbrät.

- Sven Rautenberg