Verbesserungsvorschlag bei LEFT JOIN Abfrage

[jaegerschnitzel]

MySQL Verbesserungsvorschlag bei LEFT JOIN Abfrage

Ich habe zwei Tabellen:
_news

id
id_user
datum
titel
news

_comments

id
id_news
kommentar
datum

Nun will ich eine Liste mit meinen News ausgeben lassen. Zuerst habe ich mir ein Array mit allen News von der DB geholt und alle News in einer Schleife ausgeben lassen. Dann habe ich in der Schleife zu jeder News überprüft ob Kommentare existieren und wenn ja die Anzahl ausgeben lassen.

Nun wollte ich aber alles in einer Abfrage machen, da dies bei vielen News sicherlich schneller geht und auch DB-schonender ist.

Folgendes SQL-Statement habe ich nun entwickelt, welches auch funktioniert:

"SELECT n.id, n.id_user, n.datum, n.titel, n.news, COUNT(c.comment) AS c_count FROM _news n
LEFT JOIN _comments c ON n.id = c.id_news GROUP BY n.id ORDER BY n.datum DESC"

Wollte aber fragen, ob es da ne bessere Möglichkeit gibt.
Evtl. könnte man die Kommentare nur auslesen wenn welche existieren oder sowas. Bin für alle Anregungen offen!

 

[Exceptionfault]

Vom Prinzip her ist es genau der richtige Ansatz. Nur zwei kleine Anmerkungen:

1.) Deine GROUP BY Klauses enthält lediglich die ID Spalte aus der NEWS Tabelle. mySQL mag hier nicht meckern, aber eigentlich ist das kein gültiges SQL. Laut Spezifikation müssen in der GROUP BY Klausel alle Spalten der SELECT Liste aufgenommen werden, die keiner Aggregationsfunktion entsprechen, d.h. du solltest hier auch "id_user", "datum", "titel" und "news" aufnehmen, selbst wenn es etwas mehr Schreibarbeit ist. Unter ORACLE gäbs in diesem Fall ein Syntax Fehler.

2.) Du solltest sicherstellen, dass die Spalte "id_news" in der Tabelle "comments" einen Index besitzt und im COUNT() auch auf die Spalte "id_news" anstatt "comment" referenzieren. Somit ist das Datenbanksystem theoretisch in der Lage den LEFT JOIN ohne Zugriff auf die Tabelle durchzuführen, da alle Informationen im Index vorliegen.
Generell sollten Spalten die als FOREIGN KEY genutzt werden einen Index besitzen, da diese natürlich prädestiniert für JOINS sind.

 

[jaegerschnitzel]

Vom Prinzip her ist es genau der richtige Ansatz. Nur zwei kleine Anmerkungen:

1.) Deine GROUP BY Klauses enthält lediglich die ID Spalte aus der NEWS Tabelle. mySQL mag hier nicht meckern, aber eigentlich ist das kein gültiges SQL. Laut Spezifikation müssen in der GROUP BY Klausel alle Spalten der SELECT Liste aufgenommen werden, die keiner Aggregationsfunktion entsprechen, d.h. du solltest hier auch "id_user", "datum", "titel" und "news" aufnehmen, selbst wenn es etwas mehr Schreibarbeit ist. Unter ORACLE gäbs in diesem Fall ein Syntax Fehler.

2.) Du solltest sicherstellen, dass die Spalte "id_news" in der Tabelle "comments" einen Index besitzt und im COUNT() auch auf die Spalte "id_news" anstatt "comment" referenzieren. Somit ist das Datenbanksystem theoretisch in der Lage den LEFT JOIN ohne Zugriff auf die Tabelle durchzuführen, da alle Informationen im Index vorliegen.
Generell sollten Spalten die als FOREIGN KEY genutzt werden einen Index besitzen, da diese natürlich prädestiniert für JOINS sind.

OK danke, werde ich soweit alles berücksichtigen.

Dann habe ich noch drei Fragen:
-Kann ich auch automatisch einen Index erzeugen lassen wenn eine Spalte als FOREIGN KEY benutzt wird?
-Wie muss ich mein Statement erweitern wenn ich noch eine Kategorieabfrage hinzunehmen möchte (Kategorien sind ähnlich wie Kommentare aufgebaut)?
-Ist die Performance bei vielen News mit den Joins schneller als 2 separate Abfragen?

 

[Exceptionfault]

-Kann ich auch automatisch einen Index erzeugen lassen wenn eine Spalte als FOREIGN KEY benutzt wird?

In Oracle nicht. In mySQL bin ich mir nicht sicher, aber spontan würde ich nein sagen, zumal FOREIGN KEYs in mySQL auch von der verwendeten Storage Engine abhängig sind.
MyISAM kann z.B. überhaupt keine Foreign Keys. InnoDB kann Foreign Keys, ich weiss aber nicht ob mySQL hier selbst Indizes anlegt oder nicht.

-Wie muss ich mein Statement erweitern wenn ich noch eine Kategorieabfrage hinzunehmen möchte (Kategorien sind ähnlich wie Kommentare aufgebaut)?

Mit einem weiteren LEFT JOIN, also z.B. so:

SELECT  n.id, n.id_user, n.datum, n.titel, n.news, 
        COUNT(c.id_news) AS c_count,
        COUNT(k.id_news) AS c_kategorie
FROM    _news n
LEFT JOIN _comments c 
ON      n.id = c.id_news 
LEFT JOIN _ketagorien k
ON      n.id = k.id_news
GROUP BY n.id, n.id_user, n.datum, n.titel, n.news 
ORDER BY n.datum DESC

-Ist die Performance bei vielen News mit den Joins schneller als 2 separate Abfragen?

Das lässt sich schwer sagen da es sehr stark vom verwendeten Datenbanksystem und deiner Umgebung abhängt. Folgende Punkte spielen hierbei eine Rolle:

• Welchen Algorithmus nutzt das Datenbanksystem bei großen JOINS (Nested Loops oder Hash Joins..)
• Wie hoch ist der Overhead der Datenbank zum parsen eines Statements
• Wie groß ist der Overhead im Netzwerk zwischen Datenbank un Client

Generell sollte der JOIN schneller sein, da du immer mit nur einem Statement auskommst. Bei Abfrage der Kommentare in einem LOOP ist der Aufwand linear steigend mit der Anzahl der News, also (1+x).

 

[jaegerschnitzel]

hab nochmal ein kleines problem
da ich nun mehrere kategorien pro News ermögliche habe ich noch eine Zwischentabelle _news_category erstellt.

Nun bekomme ich die Abfrage aber nicht mehr richtig hin. Die Anzahl der Kommentare stimmt nicht mehr und auch die Namen der Kategorien werden nicht richtig ausgelesen.
Wenn z.B. zwei Kategorien pro News vorliegen sollten beide ausgegeben werden. Das lässt sich wohl nur mit einer Unterabfrage lösen oder? Falls ja wie geht das?

EDIT:
Hab mich ein bisschen umgehört und herausgefunden, dass sich das Problem nur mit einer 2ten Abfrage lösen lässt.
Damit hab ich es jetzt hinbekommen.