Performanter SQL

[Nikolas]

Hallo Leute,

ich habe eine kleine MYSQL-Datenbank und davor eine PHP-Plattform. Dabei gibt es immer wieder mal Übersichten zu programmieren.

Szenario: Ich möchte eine Liste aller Benutzer erstellen und zu jedem Benutzer wissen, wieviele Artikel er geschrieben hat und wieviele Freunde er hat ...

Jetzt habe ich 2 Arten, wie ich das programmieren kann:

Ich mache einen großen SQL

SELECT *,  
(SELECT count(*) FROM FRIENDS f WHERE b.uid = f.uid) AS FRIENDS,  
(SELECT count(*) FROM ARTIKEL a WHERE b.uid = a.uid) AS ARTIKEL,  
 FROM BENUTZER b

ODER ich hole alle Benutzer raus, loope darüber und führe für jeden Benutzer die beiden Subselects aus.

Welche der beiden Arten ist denn am schnellsten bzw. empfehlenswertesten oder gibt es noch eine andere Möglichkeit den SQL aufzubauen - ich hab da an JOINs gedacht, aber dafür hab ich einfach kein Händchen.

LG Nikolas

[hotti]

hi,

Welche der beiden Arten ist denn am schnellsten bzw. empfehlenswertesten oder gibt es noch eine andere Möglichkeit den SQL aufzubauen - ich hab da an JOINs gedacht, aber dafür hab ich einfach kein Händchen.

Zu Joins gibt es einen Artikel in SELF. Deine Abfrage wird ein Join sein, schau Dir mal den Artikel an, das kriegst Du auch hin.

Hotti

[Vinzenz Mai]

Hallo,

Szenario: Ich möchte eine Liste aller Benutzer erstellen und zu jedem Benutzer wissen, wieviele Artikel er geschrieben hat und wieviele Freunde er hat ...

SELECT *,
-- benötigst Du wirklich alle Spalten aus der Tabelle benutzer?

(SELECT count() FROM FRIENDS f WHERE b.uid = f.uid) AS FRIENDS,
(SELECT count() FROM ARTIKEL a WHERE b.uid = a.uid) AS ARTIKEL,
FROM BENUTZER b

  

> ODER ich hole alle Benutzer raus, loope darüber und führe für jeden Benutzer die beiden Subselects aus.  
  
das ist höchstwahrscheinlich deutlich langsamer.  
  

> Welche der beiden Arten ist denn am schnellsten bzw. empfehlenswertesten  
  
EXPLAIN befragen, messen, optimieren, erneut EXPLAIN befragen, erneut messen ...  
  

> oder gibt es noch eine andere Möglichkeit den SQL aufzubauen - ich hab da an JOINs gedacht, aber dafür hab ich einfach kein Händchen.  
  
Freundliche Grüße  
  
Vinzenz

[Ilja]

moin,

ich hab da an JOINs gedacht, aber dafür hab ich einfach kein Händchen.

JOINS wäre für dich in diesem fall der falsche weg, arbeite wie in deinem beispiel mit korrelierten unterabfragen, falls nicht vorhanden setze INDEXE auf deine Fremdschlüssel und du bist glücklich.

Ilja

[Cheatah]

Hi,

ODER ich hole alle Benutzer raus, loope darüber und führe für jeden Benutzer die beiden Subselects aus.

Faustregel: Führe immer so wenige SQL-Statements wie möglich aus. Führe *nie* SQL-Statements in einer Schleife aus.

Cheatah

[MarkusFröhlich]

SELECT *,

(SELECT count() FROM FRIENDS f WHERE b.uid = f.uid) AS FRIENDS,
(SELECT count() FROM ARTIKEL a WHERE b.uid = a.uid) AS ARTIKEL,
FROM BENUTZER b

>   

~~~sql
  
So würde ich es machen:  
  
   select count(distinct f.uid) as Friends  
          count(distinct a.uid) as Artikel  
           from Benutzer b  
left outer join FRIENDS f on (b.uid = f.uid)  
left outer join FRIENDS a on (b.uid = a.uid)  

So hast du ohne Probleme zugriff auf alle Tabellen.
Und da alle Uid eh einen Index haben sollte es auch keine Performace Probleme geben.

[Ilja]

moin,

So würde ich es machen:

select count(distinct f.uid) as Friends
          count(distinct a.uid) as Artikel
           from Benutzer b
left outer join FRIENDS f on (b.uid = f.uid)
left outer join FRIENDS a on (b.uid = a.uid)
[/code]

So hast du ohne Probleme zugriff auf alle Tabellen.
Und da alle Uid eh einen Index haben sollte es auch keine Performace Probleme geben.

ich kann von dieser idee nur abraten, joins sind böse und verleiten uns, so das wir wandern in dunkelheit....also bevor ich hier anfange zu prädigen, kann dein weg gar nicht funktionieren. es ist die tükische falle zugriff auf beestimmte spalten haben zu wollen und sie dann einfach zu joinen, sei es nun über einen INNER oder OUTER join. aber überlg dir mal was passiert, wenn eine benutzer mehrere freunde hat, wiewillst du den  mit deinem Count und ohne zu gruppieren dann auch noch andere spalten ausgeben, performance wird auch schlechter sein....

Ilja

[MarkusFröhlich]

moin,

So würde ich es machen:

select count(distinct f.uid) as Friends
          count(distinct a.uid) as Artikel
           from Benutzer b
left outer join FRIENDS f on (b.uid = f.uid)
left outer join FRIENDS a on (b.uid = a.uid)
[/code]

So hast du ohne Probleme zugriff auf alle Tabellen.
Und da alle Uid eh einen Index haben sollte es auch keine Performace Probleme geben.

ich kann von dieser idee nur abraten, joins sind böse und verleiten uns, so das wir wandern in dunkelheit....also bevor ich hier anfange zu prädigen, kann dein weg gar nicht funktionieren. es ist die tükische falle zugriff auf beestimmte spalten haben zu wollen und sie dann einfach zu joinen, sei es nun über einen INNER oder OUTER join. aber überlg dir mal was passiert, wenn eine benutzer mehrere freunde hat, wiewillst du den  mit deinem Count und ohne zu gruppieren dann auch noch andere spalten ausgeben, performance wird auch schlechter sein....

Ilja

Joins sind böse?????
Wie greifst du dann auf 2 Tabellen gleichzeitig zu?
Performanceverlust? Alle ids haben automatisch einen Index.
Bei dem select kann man auch eh einfach eine group by Anweisung hinzufügen.

Zeig mal bitte deinen Lösungsvorschlag?

So wärs mit group by richtig:

select Benutzer,
           count(distinct f.uid) as Friends,
           count(distinct a.uid) as Artikel
      from Benutzer b
 left outer join FRIENDS f on (b.uid = f.uid)
 left outer join FRIENDS a on (b.uid = a.uid)
      group by Benutzer

[Ilja]

moin,

wenig zeit im moment, deswegen erst eine spätere antwort.

Joins sind böse?????

ja, das ist ein guter grundsatz, nach dem man sich richten sollte. das problem an joins ist die gefahr der vervielfältigung der datensätze in der ergebnismenge, so dass du wie in deinem fall mit DISTINCT arbeiten musst, was performance kostet, um das wieder "gerade zu biegen". du wählst quasi erst die datensätze in der selektionn aus, um sie später wieder zu verwerfen.

natürlich kann man nicht immer auf joins verzichten, zum beispiel weil man genau diese vervielfältigung haben will oder aus anderen gründen. oftmals aber kommt diese vervielfältigung ganz unbewußt zustande, weil jemand noch eine weitere spalte aus einer anderen tabelle in der projektion (ausgabe) anzeigen will und deswegen eine weitere tabelle joint. und schon ist es geschehen, die anzahl der datensätze stimmt nicht mehr.

das prinzip ist, nicht wahllos joinen, sondern erst mal muss die anzahl der datensätze über die selektion stimmen. dann erst sollte man sich gedanken über die projektion machen und nicht umgekehrt.

Wie greifst du dann auf 2 Tabellen gleichzeitig zu?

joins sind nicht der einzige weg, tabellen miteinander zu verknüpfen. unterabfragen haben zwar einen schlechten ruf, aber das ganz zu unrecht. sie sollten das bevorzugte mittel sein.

Performanceverlust? Alle ids haben automatisch einen Index.

auch die unterabfragen werden den index nutzen, aber sie brauchen nicht noch zusätzlich über einen DISTINCT die doppelten datensätze raus filtern und vorher die Datensätze durch das GROUP BY zu sortieren.

Bei dem select kann man auch eh einfach eine group by Anweisung hinzufügen.

viele wege führen nach rom, und auch wenn das gleiche ergebnis raus kommt, so sind nicht alle wege gut. GROUP kostet, genauso wie DISTINCT gibt es solche funktionalitäten nicht umsonst.

Zeig mal bitte deinen Lösungsvorschlag?

du hast dich bei deinem GROUP BY offenbar verschrieben, willst sicherlich nicht zweimal auf die FRIENDS tabelle joinen, sondern das andere mal auf die artikel.

SELECT b.*,
       (SELECT COUNT(*) FROM FRIENDS f WHERE b.uid = f.uid) Freunde,
       (SELECT COUNT(*) FROM ARTIKEL a WHERE b.uid = a.uid) Artikel
FROM Benutzer b
;

Das ist alles, was du brauchst, sieht doch schnuckelig aus....

Ilja