Left Join, Group By - Leere Werte nicht zusammenfassen?

[Emanuelle]

Hallo,
ich habe zwei Tabellen die ich nicht verändern kann, in grob sehen sie so aus

Code: Select all

TABLE navigation
id, parent_id, parents_id_path, name

TABLE tasks
id, project_id->nav.id, description, status

Meine Abfrage ist grob folgende:

Code: Select all

SELECT n.id, name COUNT(t.Description), t.id
FROM navigation n LEFT JOIN tasks t
   ON n.id = project_id
GROUP BY project_id
ORDER BY parents_id_path

Die Anzeige fasst jetzt die Knoten zusammen und zeigt die Anzahl der zugehörigen Tasks an. Das Problem: alle Navigationspunkte OHNE zugehörige Tasks sind zusammengefasst, sollen aber einzelnd aufgeführt werden (halt Navigation)

Kennt da jemand eine Möglichkeit das ohne zusätzliche Abfragen, Tabellen(-änderungen) oder ähnliches zu lösen?

Liebe Grüße
Emma

[Nobbie]

Diese Abfrage strotzt vor Fehlern, dass MySQL das überhaupt durchläßt ist eigentlich schon unverständlich. Bei gruppierten Selects kann man nur nach der gruppieren Spalte selektieren und nicht noch nach anderen Spalten, sondern nur nach sog. Aggregatfunktionen der anderen Spalten (MAX(spalte), MIN(spalte), COUNT(Spalte) usw.). Mit anderen Worten:

der Select nach t.id ist vollkommen ungültig (im ANSI SQL ist das ein Abbruchfehler), MySQL führt die Anfrage aus und liefert einen Zufallswert aus. Entweder streiche diese Spalte oder (ich weiß ja nicht, was da hin soll) nimm MAX(t.id) oder MIN(t.id)

Außerdem solltest Du nicht nach project_id gruppieren, sondern nach n.id, das ist ja auch die selektierte Spalte. Die ORDER Klausel ist dem Zusammenhang auch völlig sinnlos, weil durch die Gruppierung die Reihenfolge belanglos ist. MySQL liefert da möglicherweise zufällig für t.id den gewünschten Wert, aber das SQL ist schlicht falsch.

Wenn die Gruppierung und der Select richtig ist, sollte auch das Ergebnis sich verbessern (dieses "name" habe auch rausgeworfen, was ist das?):

SELECT n.id, COUNT(t.Description)
FROM navigation n LEFT JOIN tasks t
ON n.id = t.project_id
GROUP BY n.id

[Emanuelle]

Hui ui ui,,, das klingt ja vernichtend...

Diese Abfrage strotzt vor Fehlern, dass MySQL das überhaupt durchläßt ist eigentlich schon unverständlich. Bei gruppierten Selects kann man nur nach der gruppieren Spalte selektieren und nicht noch nach anderen Spalten, sondern nur nach sog. Aggregatfunktionen der anderen Spalten (MAX(spalte), MIN(spalte), COUNT(Spalte) usw.). Mit anderen Worten:

der Select nach t.id ist vollkommen ungültig (im ANSI SQL ist das ein Abbruchfehler), MySQL führt die Anfrage aus und liefert einen Zufallswert aus. Entweder streiche diese Spalte oder (ich weiß ja nicht, was da hin soll) nimm MAX(t.id) oder MIN(t.id)

Da ich leider nur MySQL habe, gibt es einen strict-mode oder einen validator womit ich sowas zumindest die groben Fehler erwische?

Außerdem solltest Du nicht nach project_id gruppieren, sondern nach n.id, das ist ja auch die selektierte Spalte. Die ORDER Klausel ist dem Zusammenhang auch völlig sinnlos, weil durch die Gruppierung die Reihenfolge belanglos ist. MySQL liefert da möglicherweise zufällig für t.id den gewünschten Wert, aber das SQL ist schlicht falsch.

Das wäre leider auch falsch, ich hätte es viel ausführlicher schreiben sollen. Nach project_id habe ich gruppiert weil dann alle 'Tasks' zu einem Knoten der Navigation table zusammengefasst werden.
Das Order By ergab sich aus der Tatsache das dort im Grunde die parent id's bis zum root gecached werden und ich damit dann tatsächlich die Baumstruktur in der richtigen Reihenfolge sortieren kann.

Wenn die Gruppierung und der Select richtig ist, sollte auch das Ergebnis sich verbessern (dieses "name" habe auch rausgeworfen, was ist das?):

Für das Beispiel war es wirklich nicht wichtig, dort enthalten ist aber der Text, der Name des Navigationspunktes. Im Beispiel ist es wirklich unnötig.

SELECT n.id, COUNT(t.Description)
FROM navigation n LEFT JOIN tasks t
ON n.id = t.project_id
GROUP BY n.id

naja, warum das jetzt nicht klappt habe ich ja oben mehr oder weniger gut darstellen können. Entschuldige bitte die viel zu kurze und nicht vollständige Beschreibung des Problems. Eindeutig mein Fehler - sorry

Mittlerweile habe ich auch eine - zumindest bei MySQL korrekt ausgebende SQL Anweisung gefunden:

Code: Select all

SELECT id, name, path_text, counted
FROM navigation LEFT JOIN (
   SELECT project_id, COUNT(id) AS counted
   FROM tasks
   GROUP BY project_id) task
ON id = project_id
ORDER BY path

Also, in der inneren Abfrage müsste ich auch das id irgendwie bei der group by Anweisung verwursten, richtig? Oder ist das aufgrund des COUNT doch erlaubt?

Sorry, MySQL ist halt ... Standard irgendwie... (ich meine hier Programmtechnisch, nicht SQL STandard 2003 oder so)

Danke für die bisher aufgeführten Punkte. Ich hoffe das du dir auch weiterhin die Mühe gibst mir zu helfen, auch wenn ich nicht mehr als die paar Pixel bieten kann, die das Wort Danke abbilden.

Liebe Grüße
Emma

[Nobbie]

Da ich leider nur MySQL habe, gibt es einen strict-mode oder einen validator womit ich sowas zumindest die groben Fehler erwische?

NIcht dass ich wüßte, das ist halt MySQL. Wenn Du Oracle oder Informix benutzt, fliegst Dir um die Ohren... (leider habe ich noch nie mit PostGres gearbeitet - vielleicht ist der Interpreter ja strenger).

SELECT n.id, COUNT(t.Description)
FROM navigation n LEFT JOIN tasks t
ON n.id = t.project_id
GROUP BY n.id

naja, warum das jetzt nicht klappt habe ich ja oben mehr oder weniger gut darstellen können. Entschuldige bitte die viel zu kurze und nicht vollständige Beschreibung des Problems.

"Klappen" sollte das aber - wenn die Tabellen den Inhalt haben, den ich glaube, welchen sie haben (boah ey was für ein Satz...), dann sollte dieser Select zu jeder navigation id die Anzahl Projekte ausgeben (mehr allerdings nicht).

Mittlerweile habe ich auch eine - zumindest bei MySQL korrekt ausgebende SQL Anweisung gefunden:

Code: Select all

SELECT id, name, path_text, counted
FROM navigation LEFT JOIN (
   SELECT project_id, COUNT(id) AS counted
   FROM tasks
   GROUP BY project_id) task
ON id = project_id
ORDER BY path

Also, in der inneren Abfrage müsste ich auch das id irgendwie bei der group by Anweisung verwursten, richtig? Oder ist das aufgrund des COUNT doch erlaubt?

Da gruppierst Du ja erst die tasks und gibst die Anzahl pro Projekt_id aus. Und dann erst wird der LEFT JOIN mit der Navigation durchgeführt (beim anderen Select wirkt die Gruppierung auf die gejointe Tabelle, hier aber wird erst gruppiert und dann das Ergebnis geJOINT).

Der einzige Vortei der zweiten Variante ist, dass Du im äüßeren Select noch die anderen Spalten aus der Tabelle Navigation selektieren kannst, das ist aber auch schon der einzige Unterschied. Dieser Select gibt mehr Spalten und Text, aber rein die Struktur müßte dieselbe sein, es wird für jede Navigations id die Anzahl tasks angezeigt (und in der jetzigen Lösung eben auch noch Name und Path - was auch immer das ist).

Die Spalte COUNT(id) darfst Du im inneren Select selektieren, ohne eine entsprechende GROUP Klausel zu haben, weil COUNT() eine sog. Aggregatfunktion ist. Aggregatfunktionen sind Funktionen, die für ALLE Mitglieder einer Gruppe (also alle Zeilen, die die Bedingung erfüllen, dass sie in einer bestimmten Spalte gleiche Werte haben) eine Berechnung anstellen. COUNT() ist eine sehr einfache Aggregatfunktion, sie berechnet die Anzahl der Zeilen dieser Gruppe. Es gibt (wie gesagt) noch viele andere Aggregatfunktionen, beispielswiese MIN(spalte), die gibt den kleinsten Wert der Spalte aus (immer jeweils innerhalb einer Gruppe), MAX(spalte) gibt den größten Wert aus, AVG(spalte) gibt den Durchschnitt aus, wobei spätestens dort die Spalte numerisch sein muss) usw.

Bei COUNT() braucht man übrigens keinen Spaltennamen anzugeben, da sich die Funktion nicht auf den Inhalt der Spalte bezieht (anders als bei AVG() beispielsweise), weswegen man auch einfach COUNT(*) schreiben kann. Ich persönlich finde das lesbarer, wobei ich den * im Select wiederum (also "Select * ....") grausam finde, das ist keine Art zu programmieren.

[Emanuelle]

Ich bin echt grausam...

naja, schade das es bei MySQL sowas nicht zu geben scheint - daher sogar doppelten Dank für deine Hilfe!!!

dann sollte dieser Select zu jeder navigation id die Anzahl Projekte ausgeben (mehr allerdings nicht).

Das ist korrekt, bei den Knoten steht erstmal nur die Anzahl der zugehörigen Tasks, diese lassen sich dann wieder per klick auf oder zuklappen. Standard ist zu, bei etwa 60 Knoten mit jeweils (!) etwa 20-120 Tasks wäre es sonst eine unüberschaubare Datenvielfalt - so ist es ja bereits extrem und alles andere als schön.

Da gruppierst Du ja erst die tasks und gibst die Anzahl pro Projekt_id aus. Und dann erst wird der LEFT JOIN mit der Navigation durchgeführt (beim anderen Select wirkt die Gruppierung auf die gejointe Tabelle, hier aber wird erst gruppiert und dann das Ergebnis geJOINT).

Naja, Sinn und Zweck war die 8 Knoten ohne Tasks nicht zu verlieren, genau diese wurden im Eingangsquery zusammengefasst und zu einem Knoten verschmolzen. Darauf sollte die Frage im ersten Posting abzielen. Ich sollte versuchen eher mittags zu posten, das sollte dann nicht nur les- sondern auch korrekt interpretierbare Postings ergeben.

Der einzige Vortei der zweiten Variante ist, dass Du im äüßeren Select noch die anderen Spalten aus der Tabelle Navigation selektieren kannst, das ist aber auch schon der einzige Unterschied. Dieser Select gibt mehr Spalten und Text, aber rein die Struktur müßte dieselbe sein, es wird für jede Navigations id die Anzahl tasks angezeigt (und in der jetzigen Lösung eben auch noch Name und Path - was auch immer das ist).

Das war auch gewünscht, nur mache ich Dinge gerne richtig, und so ist es vermutlich noch immer nicht ganz rund - aber runder. Zumindest ist dein Hinweis "Bei gruppierten Selects kann man nur nach der gruppieren Spalte selektieren und nicht noch nach anderen Spalten" ist eingearbeitet ^^
Ein komplexes Query dürfte besser sein als die zig weniger komplizierten die sonst nötig wären um an die erforderlichen Infos zu kommen.

Die Spalte COUNT(id) darfst Du im inneren Select selektieren, [...]

Bei COUNT() braucht man übrigens keinen Spaltennamen anzugeben, [...]

Danke, auch eine gute Erläuterung die weiterhilft, und der Tipp mit dem Spaltennamen im Count ist zwar nicht unbedingt Gold wert, aber das Sahnehäubchen auf der Wahnsinnstorte die du mir geliefert hast.

Danke, danke, danke

Emma

[Nobbie]

Ein komplexes Query dürfte besser sein als die zig weniger komplizierten die sonst nötig wären um an die erforderlichen Infos zu kommen.

Das glaube ich ehrlich gesagt nicht - eher das Gegenteil.

Warum?

a) komplexe Querys versteht sehr schnell nur noch der, der sie entwickelt hat - und der irgendwann auch nicht mehr...

b) komplexe Querys sind oft nicht abwärtskompatibel. Ich habe beispielsweise selbst auf Basis von MySQL 3 (ja, uralt) gelernt (mit dem, aus meiner Sicht besten SQL Buch welches es gibt, das "MySQL Kochbuch" von Paul Dubois, aus dem O'Reilly Verlag - ein ganz tolles Buch zum allgemeinen Verständnis von SQL) und damals war ein solcher Subquery noch gar nicht möglich (bzw. manche gingen, andere nicht)

c) die Querys sind ja sowieso in irgendwelche Hochsprachen eingebettet (wahrscheinlich PHP), da kann man die Logik auch ins PHP legen.

d) viele Menschen sind der irrigen(!) Meinung, wenige Query wären schneller als viele Querys. Tatsache ist: die Anzahl Querys hat kaum einen Einfluss auf die Gesamtperformance, obwohl die Querys ja erst interpretiert werden müssen. Vor 30 Jahren wäre sicherlich dieser Denkansatz richtig gewesen - aber heutige Desktop CPUs sind so unvorstellbar schnell, dass der Aufwand für die Interpretation eines QUerys in tausendstel von Sekundenbruchteilen erledigt sind. Langsam ist dagegen immer noch der erzeugte I/O. Ich habe selbst vor einigen Jahren umfangreiche Tests und Benchmarks ausgeführt mit ganz eindeutigen Ergebnissen: langsam wird ein Query immer dann, wenn die "Fundstelle" (= die erzeugte Tabelle) sehr groß ist. Also viele Treffer hat und darin ggf. auch noch viele Spalten.

Die ideale und performanteste Programmierung (die mit Abstand schneller ist als die andere Variante), ist die, immer möglichst nur nach einer Menge Primärkeys zu suchen und im Folgeselect die Daten dazu zu ermitteln. Nimm beispielsweise eine Tabelle mit 100000 Einträgen und vergleiche die folgenden Logiken:

a) Select * from Tabelle --- und anschließender Loop fetchen über alle Daten

versus

b) Select id from Tabelle --- anschließender Loop mit fetch <id> und anschließenden Select * From Tabelle Where id = <id> (aus dem fetch)

Je größer die Tabelle ist, umso langsamer wird die Variante a) sein, weil dort mit einem Schlag eine Riesentreffermenge erzeugt wird. Wenn Du Testdaten hast, kannst Du ja mal zwei PHP Progrämmelchen schreiben und am Anfang und Ende einen Timestamp nehmen und zum Schluss die Differenz ausgeben.

Bei Tabellen mit paar Zeilen ist das natürlich egal - aber bei Massenverarbeitung werden die Unterschiede erheblich ausfallen.

[Emanuelle]

Hallo Nobbie,
es tut mir leid das meine Antwort so spät kommt. Leider konnte ich deine Performanzaussage nicht bestätigen und habe mich auf Ursachensuche begeben.

Mögliche Gründe warum es in diesem speziellen Fall mit der von mir vorgestellten Lösung schneller geht.

1. PHP unter Windows ist zwar performanter als früher, aber immer noch ein billiger Abklatsch zu Linux (und ich nutze - Schande auf mein Haupt - ganz bequem Windows)
2. Die Datensätze sind sehr klein und passen ungefiltert komplett in den CPU L3-Cache (beide Tabellen zusammen unter 2MB, Angabe phpMyAdmin)
3. die Spalten des inneren Statements, sowie die zum verknüpfen mit dem äußeren Statements sind alle Indizes
4. Und der wichtigste: MySQL und PHP befinden sich NICHT auf dem gleichen PC oder im LAN, jede Abfrage hat also eine Verzögerung durch Latenzen die hier sicher mehr als die eigentliche Übertragung ins Gewicht fallen.

Daher vermute ich mal das sich das nicht verallgemeinern lässt. Und ja, das fast Alle Spalten als Index definiert sind ist grober Unfug und bei Veränderungen ein echter Performanzfresser. Aber darauf habe ich keinen Einfluss.

Danke trotzdem für den Hinweis, auch hier habe ich mit deiner Hilfe einen blinden Fleck mal ins Licht holen können und viel dazu gelernt.

Liebe Grüße
Emma

[Nobbie]

Die Datensätze sind sehr klein und passen ungefiltert komplett in den CPU L3-Cache (beide Tabellen zusammen unter 2MB, Angabe phpMyAdmin),

Das ist ja genau das, was ich am Ende ja geschrieben habe: "Bei Tabellen mit paar Zeilen ist das natürlich egal - aber bei Massenverarbeitung werden die Unterschiede erheblich ausfallen."

Je weniger Datensätze und/oder Spaltengröße, umso mehr spielt natürlich die Infrastruktur eine Rolle, das ist ja auch klar, weil der relative Overhead dann zunimmt. Ich würde aber so oder so vermuten, dass sich die Antwortzeiten im Zehntel (oder sogar Hundertstel) Sekundenbereich abspielen. Da machte eine Benchmarkanalyse nicht viel her...

Auch die Tatsache, dass bei "Euch" (wer oder was auch immer das ist) alle Spalten mit einem Index versehen sind, spricht dafür, dass der Datenbestand "übersichtlich" ist. Stelle Dir mal eine Datenbank (nur als Beispiel) einer Airline vor - die Anzahl Flüge, Passagiere usw. im Laufe es eines Jahres. Da entstehen wirklich richtig viele Daten - und wenn man dann mit Keys um sich wirft, als kosteteten sie nichts, dann wird man sich wundern, warum ein simpler INSERT mehrere Sekunden braucht.

Bleibt aber unter dem Strich immer noch die "Komplexität" (wobei ich nicht behaupten will, dass das hier zu komplex wäre). Aber ich habe (auch schon hier im Forum) Selects gesehen, da fallen mir die letzten Haare aus. Keine Ahnung, ob die jemals nochmal jemand versteht. Ich jedenfalls nicht.

[Emanuelle]

Das ist ja genau das, was ich am Ende ja geschrieben habe: "Bei Tabellen mit paar Zeilen ist das natürlich egal - aber bei Massenverarbeitung werden die Unterschiede erheblich ausfallen."

Ich glaube ich muss mich daran gewöhnen das man in Datenbanken mit anderen Größen rechnet, es sind schon gute 2k Zeilen die will ich nicht von Hand zählen. XD

Je weniger Datensätze und/oder Spaltengröße, umso mehr spielt natürlich die Infrastruktur eine Rolle, das ist ja auch klar, weil der relative Overhead dann zunimmt. Ich würde aber so oder so vermuten, dass sich die Antwortzeiten im Zehntel (oder sogar Hundertstel) Sekundenbereich abspielen. Da machte eine Benchmarkanalyse nicht viel her...

Hmm... aber mal angenommen eine Tabelle hätte eine Spalte die richtig Daten enthält... vermutlich schwachsinnig, aber youtube Filmchen oder mp3's oder so was... etwas was schon einige hundert MB zusammenkommen lässt... Jetzt ein SQL Statement das die Spalte nicht berührt - sind die Datenbanken in der Regel so schlau die Spalte nicht mit zu laden oder sind sie stupide und nehmen erst mal alles?

Auch die Tatsache, dass bei "Euch" (wer oder was auch immer das ist) alle Spalten mit einem Index versehen sind, spricht dafür, dass der Datenbestand "übersichtlich" ist. Stelle Dir mal eine Datenbank (nur als Beispiel) einer Airline vor - die Anzahl Flüge, Passagiere usw. im Laufe es eines Jahres. Da entstehen wirklich richtig viele Daten - und wenn man dann mit Keys um sich wirft, als kosteteten sie nichts, dann wird man sich wundern, warum ein simpler INSERT mehrere Sekunden braucht.

Übersichtlich ist sie gar nicht, ich hätte sie ganz anders aufgeteilt. Es gibt keine Abfrage, KEINE die nur eine Tabelle betrifft. Selbst wenn ein einziger Datensatz angezeigt werden soll... weil immer irgendwas ausgelagert ist, selbst wenn es eine 1:1 Verbindung ist... deswegen vermutlich die ganzen Indizes und 'caching' Spalten die Inserts und updates noch intensiver machen. Dürfte ich das hier zeigen hättest du vermutlich nicht nur die Haare verloren sondern den Verstand gleich mit. Ich habe nicht viel Ahnung, aber das ist alles andere als schön gelöst.

Bleibt aber unter dem Strich immer noch die "Komplexität" (wobei ich nicht behaupten will, dass das hier zu komplex wäre). Aber ich habe (auch schon hier im Forum) Selects gesehen, da fallen mir die letzten Haare aus. Keine Ahnung, ob die jemals nochmal jemand versteht. Ich jedenfalls nicht.

Dann drücke ich dir die Daumen das die im nachhineineinfach einfach mal amüsante Erinnerungen werden, nicht das die Haare wirklich noch ausfallen

Liebe Grüße
Emma