[SQL] COUNT (*) als Subqueries?

[Eagle-PsyX-]

Hi,

ich habe mal eine Frage.
Ich habe eine Tabelle "category" mit 2 Spalten "cat_id" und "cat_title".
Ebenso habe ich eine Tabelle "content" mit mehreren Spalten darunter auch "content_cat_id".

Hat jemand eine Lösung, wie ich alle Kategorien auflisten kann, inklusive einer Zählung der Inhalte der Kategorien.

Also z.B. als Ergebnis:

cat_id  |  cat_title  |  con_count
1         | test          |  23
2         | isnichtwa  |  5

Ok, hat erstmal geklappt, hätte ich eher zuerst probieren sollen bevor ich frage...

SELECT 
   *,
   (SELECT COUNT(*) FROM `oa_content` WHERE content_cat_id = cat_id) as con_cat_count,
   (SELECT COUNT(*) FROM `oa_content` WHERE content_scat_id = cat_id) as con_scat_count 
FROM 
   `oa_category`

Das ging irgendwie _zu einfach. Ist das Performance-lästig?

 

[natto]

Schön ist das nicht, aber wieviele Einträge haben denn deine Tabellen?

 

[Eagle-PsyX-]

Wenn du das meinst, schaue Anhang.
Aber keine Sorge, es werden deutlich mehr. Arbeite an meinen CMS gerade.

Also die Abfrage wird auch nur im Admin-Bereich betätigt. Insofern ist es nicht schlimm wenn's nicht ganz so wenig Leistung braucht.

Edit:: Das hat auf dem altem lahmen ALDI-Notebook hier 0.008 Sekunden gebraucht.

 

[DerEineDa]

Das ist eigentlich eine sehr gute Lösung. Viele Leute würden vielleicht eher einen LEFT JOIN benutzen und dann eben gruppieren. Ich sehe bei deiner Lösung aber keinen Nachteil.

 

[Rossibaer]

Hi Eagle, deine Lösung ist Klasse. Sorg nur dafür das die Spalten content_cat_id, cat_id und content_scat_id jeweils indiziert werden bzw. als primary keys definiert sind und dann wirst du auch nicht so schnell einen performance einbruch erleben. Sicher kann man diese Subselects auch in einem join auflösen, jedoch wird es etwas frimmelig, weil du hier 2 counts verwendest und der daraus entstehende join inklusive gruppieren recht kompliziert werden kann.
Grüße Rossibaer

 

[Eagle-PsyX-]

Danke.
Kennt eigentlich jemand eine gute (am besten eine deutsche, aber nicht vorrausgesetzt) Seite mit einer exakten verständlichen Erklärung der Prmary Keys/Unique Keys/Indezen/Volltext? Bzw. dessen Zusammenhang mit der Leistung von Abfragen usw.?

Finde in der MySQL Documentations nichts wirklich gescheites!

 

[Rossibaer]

Kann dir leider keine entsprechende Seite nennen, aber vielleicht hilft dir das für einen Einstieg:

Index: Ein Index wird auf eine oder mehrere Spalten einer Tabelle angewendet. Den entsprechenden Befehl kannst du in der Doku des DBMS nachlesen, er müsste so ungefähr lauten:

CREATE INDEX IndexName ON TabellenName(SpaltenListe)

Ein Index erzwingt in einer Datenbank, das alle Werte der indizierten Spalten einer Tabelle in einem internen Cache in sortierter Form (ist meist im Arbeitsspeicher) gehalten werden. Das führt dazu, das ein Zugriff auf die Tabelle mittels SELECT, INSERT, UPDATE, DELETE sehr sehr schnell erfolgen kann, weil das DBMS diese Daten für einen optimalen Zugriff bereit hält. Ganz im Gegenteil sind die Werte der nicht indizierten Spalten im schlimmsten Fall irgendwo auf einem langsamen Medium, z.B. der Festplatte, abgelegt. Das DBMS muss dann bei der Suche/beim Filtern der Datensätze entsprechend sehr teure (langsame) Zugriffe machen. Vergleich: Festplatte (Alle Daten der Tabelle) mit Arbeitsspeicher (indizierte Daten im Cache) hinsichtlich der Geschwindigkeit!

Unique Index: Ist eine besondere Form des Index, bei dem die Werte der indizierten Spalten einer Zeile jeweils eindeutig sind, d.h. es gibt keine 2. Zeile die genau die gleichen Werte hat.

PrimaryKey: Ist ein Schlüssel von einer Zeile. Aus praktischer Sicht ist das letztendlich nur eine Festlegung, das die Werte der Spalten in Ihrer Gesamtheit eindeutig für jede Zeile sind, d.h. es gibt keine 2 Datensätze, die die gleichen Werte der Spalten des PrimaryKeys haben. Ebenso fungieren Sie als der Schlüssel um eine Zeile eindeutig zu identifizieren. Diese Spalten des PrimaryKeys werden automatisch vom DBMS indiziert (s.o.). Für Abfragen oder Manipulation der Daten sollte man (wenn möglich) immer diese Spalten als Filterkriterien (in der WHERE Bedingung) verwenden um eine optimale Geschwindigkeit zu erreichen.

ForeignKey: Stellt die Beziehung zwischen 2 Tabellen her, d.h. die Spalten des ForeignKeys der Tabelle A sind mit Spalten der Tabelle B verknüpft. In der Regel ist der ForeignKey einer Tabelle auch der PrimaryKey der 2.Tabelle. ForeignKey Spalten werden nicht automatisch vom DBMS indiziert, sollten durch den Ersteller der Datenbank (du) stets mit einem Index versehen werden. Für Abfragen (insbesondere JOINS, SubSelects ... ) oder Manipulation der Daten sollte man möglichst die ForeignKeys Spalten als Filterkriterien in der WHERE Bedingung verwenden, wenn z.B. der PrimaryKey nicht eingesetzt werden kann.

Unique Keys: Kenne ich nicht und gibts die denn überhaupt?!?

Kurz gesagt, alles was im Cache einer Datenbank ist, kann sehr schnell sein, während alles andere u.U. viel Zeit braucht.

Ein Beispiel aus eigener Berufspraxis:
Die Datenbank eines meiner Kunden benötigte für einen miserablen UPDATE Befehl (keine indizierten Spalten in der WHERE Bedingung und Verwendung von Subselects!) bei 200000+ Datensätzen der Tabelle mehrere Minuten obwohl nur einige wenige Datensätze geändert wurden. Nach dem ich die betroffenen Tabellen mit den richtigen PrimaryKey, ForeignKeys und Indexen versehen hatte und die WHERE Bedingung so anpasste, das nur noch die indizierten Spalten verwendet wurden, wurde der UPDATE Befehl nach 1 Sekunde vollständig ausgeführt.

Hier noch ein paar kleine Tipps:
a) Verwende möglichst die gleiche Reihenfolge der Spalten in der Where-Bedingung, wie die Reihenfolge der Spalten des Index (der Optimizer des DBMS kann dies leichter erkennen und den Ausführungsplan, somit die Abfrage, optimieren)
b) meide Konstrukte ala " SpalteX LIKE '%XYZ%' " (führt meist zu einem sehr langsamen FULL TABLE SCAN)
c) Überprüfe den Ausführungsplan deines Sql-Befehls in einem Analyzer/Profiler (z.B. SqlAnalyzer).
Ausführung ala "INDEX SCAN" sind gut, andere wie "FULL TABLE SCAN" sind schlecht. Bei großen Tabellen auf jeden Fall, bei kleinen Tabellen auch schlecht aber meist nicht so sehr. siehe MySql EXPLAIN für die Optimierung von Abfragen

Ich hoffe, das ich dir ein klein wenig helfen konnte. Wie gesagt, das ist nur eine grobe Zusammenfassung über dieses Thema und hat keinesfalls den Anspruch auf Vollständigkeit und 100% Richtigkeit! Für Verbesserungen können sich die Experten gerne zu Wort melden.

Grüße Rossibaer

 

[darkservant2]

Das ist alles auch nur so halb korrekt im Bezug auf Indexes....

Im Ram werden die Dinger nicht gehalten - wär auch blöd, wenn der Index weg wäre wenn der Server rebootet Indexes werden einfach extra in einer effizienteren Form, die für Suchabfragen optimiert sind, abgespeichert (z.b. Binary-Trees und ähnliches). Wenn dann ein Suche läuft, wird in dieser optimieren Struktur der richtige Datensatz rausgesucht, anstatt auf der kompletten Tabelle von vorne durchzugucken.
Das hat aber erstmal gar nichts mit dem Speichermedium zu tun, die liegen auch nur auf einer langsamen Festplatte. Im Ram können Ergebnisse von Abfragen gecacht werden - das ist wieder eine ganz andere Sache.

ForeignKeys sind eher um Datenintegrität zu gewährleisten. Wenn ein DBMS dieses Feature unterstützt, kann es automatisch "motzen", wenn du z.B. versuchst einen Datensatz zu löschen, der von einer anderen Tabelle über einen ForeignKey referenziert wird.

Ein Unique Key ist genau das was es sagt: Ein Schlüssel, der einen Datensatz EINDEUTIG identifiziert. Es kann also nicht zwei Datensätze mit genau diesem Key geben.

Cache hat mit Indexen mal gar nichts zu tun. Alle abfragen die im Cache sind, sind verdammt schnell - richtig. Das können aber auch nicht indizierte Abfragen sein, weil einfach die selbe scheiß langsame Abfrage laufend kommt und das Ergebnis schon "bekannt" ist und im Cache liegt.

 

[Rossibaer]

Zum Teil stimme ich mit dem überein, was du da schreibst, komisch nur das die Antwortzeiten nach einem Server Reboot bzgl. Suche auf indizierten Spalten immer noch abweichend zu dem sind, wie wenn schonmal gesucht wurde. Ich stimme damit überein das das DBMS den Index in einer bestimmten Sortierung auf der Platte hält, dennoch landen diese Daten im Cache. Ein Cache ist per Definition nicht auf ein Bauteil begrenzt, siehe Wikipedia http://de.wikipedia.org/wiki/Cache. Es ist ein Container um einen schnellen Zugriff auf Daten zu erhalten, die redundant gespeichert sind. Sowohl einmal auf der Platte wie die restlichen Daten, als auch in diesem Container für schnellen Zugriff optimiert. Ein Binärer Baum wäre eine mögliche Organisation des Caches, ebenso könnte es eine Hashtable sein. Es ist nur eine Frage der Sichtweise / Implementierung. Wo dann tatsächlich diese Daten gehalten werden, ist eine Frage der Implementierung.

Unique Key: man könnte auch gleich Primary Key sagen. Mir war bis heute nur die Bezeichnung Primary Key bekannt. Man lernt nie aus.

Ok, dann will ich doch mal etwas zum Thema Cache = Index, an einem Beispiel schreiben:
Reboot Server, Abfrage auf Tabelle Auftrag mit nicht indizierter Spalte BESTELL_MENGE
1. SELECT * FROM AUFTRAG WHERE BESTELL_MENGE = 50
-> Abfrage langsam, da nicht im Cache (>1 Minute)
2. SELECT * FROM AUFTRAG WHERE BESTELL_MENGE = 50
-> Abfrage schnell, da noch im Cache (<1 Sekunde)
3. SELECT * FROM AUFTRAG WHERE BESTELL_MENGE = 20000
-> Abfrage langsam, da nicht im Cache (>1 Minute)

Reboot Server, nun gleiches Spiel aber mit indizierter Spalte AUFTRAGS_ID:
4. SELECT * FROM AUFTRAG WHERE AUFTRAGS_ID = 22
-> Abfrage schnell, <2 Sekunden
5. SELECT * FROM AUFTRAG WHERE AUFTRAGS_ID = 22
-> Abfrage schnell, <1 Sekunde
6. SELECT * FROM AUFTRAG WHERE AUFTRAGS_ID = 787463
-> Abfrage schnell, <1 Sekunde

Erkläre mir bitte, warum das 4. SELECT vom Auftrag 2 Sekunden dauert, während das 6. SELECT die annähernd gleiche Zeit hat wie Abfrage 5. Wenn ich da mal eins und eins zusammen zähle, würde ich sagen das Indizes aufgrund Ihrer relativ kleinen Datenmenge gegenüber der gesamten Tabelle anders behandelt werden, vielleicht sogar vollständig in den Cache wandern, während die nicht indizierten Daten nur Seitenweise eingeladen werden. Diese pauschale Aussage "Cache hat mit Indexen mal gar nichts zu tun. " kann ich leider nicht teilen.

 

[DerEineDa]

Wenn darkservant sagt "Cache hat mit Indexen mal gar nichts zu tun", dann hat er per Definition recht. Dass DBMS Ergebnisse cachen hat mit einem Index nichts zu tun. Dass sie indizierte Daten beim Starten schon in den Speicher laden mag auch oft sein, aber ändert nichts daran, dass sie dies per Definition nicht unbedingt tun müssen.

 

[Rossibaer]

Dann betrachten wir das mal von einer anderen Seite. In Wikipedia lese ich da:

Quelle: http://de.wikipedia.org/wiki/SQL
Der SQL-Standard definiert Indizes überhaupt nicht, so dass die entsprechenden CREATE INDEX und DROP INDEX Anweisungen immer produktspezifische Erweiterungen sind. Allerdings verwenden die meisten DBMS die gleiche oder eine sehr ähnliche Syntax.

Kann vielleicht nur Schund sein, aber wie kann etwas per Definition richtig sein, wenn es keine Definition als solche gibt, sondern vielmehr im Interpretationsspielraum dessen liegt, was die Hersteller als richtig oder falsch erachten? Wenn ein Hersteller entscheidet einen Standard um ein nützliches Feature zu erweitern, dann spielt hier die Implementierung dessen eine sehr große Rolle. Aber sei es drum.

 

[DerEineDa]

Ich meine mit der Definition auch nicht den SQL-Standard, sondern im Allgemeinen die Begriffe Index und Cache.

 

[PW-toXic]

SELECT
    count(*), b.cat_title
FROM
   content a, category b
WHERE
   a.content_cat_id = b.cat_id
GROUP BY
    b.cat_title

Erkläre mir bitte, warum das 4. SELECT vom Auftrag 2 Sekunden dauert, während das 6. SELECT die annähernd gleiche Zeit hat wie Abfrage 5. Wenn ich da mal eins und eins zusammen zähle, würde ich sagen das Indizes aufgrund Ihrer relativ kleinen Datenmenge gegenüber der gesamten Tabelle anders behandelt werden, vielleicht sogar vollständig in den Cache wandern, während die nicht indizierten Daten nur Seitenweise eingeladen werden. Diese pauschale Aussage "Cache hat mit Indexen mal gar nichts zu tun. " kann ich leider nicht teilen.

Cache und Index sind zwei wesentlich voneinander verschiedene Konzepte. Beide gehören jedoch in die Performanz Ecke, und sind beide voneinander unabhängig und können somit problemlos kombiniert werden, ohne von der Existenz des anderen Performanz konzepts zu wissen.

1. Anfrage: Dauert sehr lange, da alle Datensätze durchsucht werden müssen. Dafür müssen alle "Seiten" in den Speicher wandern.
2. Anfrage: Sehr schnell, weil der RAM scheinbar groß genug ist um alle Datensätze von dieser Tabelle im RAM zu halten
Zusätzlich kommen noch SQL-Abfragen spezifische Caching Mechanismen zum Einsatz, die möglicherweise das Ergebnis der Anfrage selbst cachen, und somit die Anfage nicht einmal praktisch ein 2. mal ausgeführt wird.
3. Anfrage: Wie 1. Anfrage

4. Anfrage: Sehr schnell, da es einen Index von AUFTRAGS_ID hat, und dieser nur teilweise geladen werden muss. Gäbe es keinen Index, und es gäbe 10 000 000 000 (100^5) Daten in der Tabelle, dann müssten ohne Index 10 000 000 000 Datensätze geladen werden. Bei einem B-Baum werden oftmals ca 100 Datensätze pro Seite gespeichert. Also muss man 100 000 000 Seiten aus der Tabelle laden.
Hat man jedoch einen Index, dann muss man nur soviele Seite laden, wie man auf den Weg zum gewünschten Knoten braucht. Das wären bei einem B-Baum mit ca 100 Datensätze pro Knoten log_100(100^5) = 5
Man muss also nur 5 Seiten in den Arbeitsspeicher einlagern.. Das geht verständlicherweise ziemlich schnell.. Verbraucht dabei auch noch sehr wenig speicherplatz, was wahrscheinlich zur folge hat dass diese Seite noch eine Weile im Speicher bleiben.
5. Anfrage: Sehr sehr schnell, weil die Seiten für den Index noch im Arbeitsspeicher sind, und nichtmehr extra geladen sind. Der Flaschenhals bei Datenbank abfragen sind die Festplatten zugriffe.. Diese werden aber in diesem Fall gecached.
6. Anfrage Auch sehr sehr schnell.. wie in Anfrage 4. Man kann hier jedoch keine genaueren Aussagen machen. Ich schätze jedoch, dass der Index auf die Bestell_Menge nicht unerheblich kleiner ist.

 

[Rossibaer]

Index = Inhaltsverzeichnis eines Buches um die Seiten schneller zu finden.
Cache = Container in dem Daten zwischen gespeichert werden um schneller drauf zu zugreifen.

Beides Konzepte um die Performance zu steigern. Den Index kann ich ebenfalls als einen Container ansehen in dem Daten gespeichert sind um schneller drauf zu zugreifen. Der Knackpunkt ist, das beim Index nicht die Daten der gesamten Tabelle drin stehen, sondern nur die indizierten Spalten inklusive Link auf die Zeilen der Tabelle. Im übrigen habe ich nicht gesagt, das ein Index ein Cache ist. Es war missverständlich von mir ausgedrückt. BTW: Ein Cache muss nicht im RAM sein, kann auch ein gesonderter Platz auf der Platte oder sonst wo sein.

Zu meinem Beispiel von oben:
1. Abfrage (Bestell Menge nicht indiziert): langsam weil alle Datensätze auf der Platte durchsucht werden müssen
2. Abfrage (Bestell Menge nicht indiziert): sehr schnell, weil die Datensätze der ersten Abfrage im Cache
3. Abfrage (Bestell Menge nicht indiziert): sehr langsam, weil wieder alle Datensätze auf der Platte durchsucht werden müssen
4. Abfrage (Auftrags Nr indiziert): relativ schnell, weil über Index gesucht wird, Index wird gecached
5. Abfrage (Auftrags Nr indiziert): sehr schnell, weil Abfrageergebnis im Cache
6. Abfrage (Auftrags Nr indiziert): sehr schnell, weil Index im Cache

Das ist zumindest das, was ich versuchte zu erklären.

PS: Habe bei der 6. Abfrage einen Fehler gehabt, weil da als Bedingung die Bestellmenge statt AuftragsNr drin stand, sorry.

Ergänzung in eigener Sache:
ForeignKey: Wie von Darkservant (Danke für die Richtigstellung/Ergänzung) geschrieben, dienen sie zur Sicherstellung der Integrität der Datensätze. Jedoch gibt es bei verschiedenen DBMS die Option "ON DELETE CASCADE" und "ON UPDATE CASCADE", welche dem DBMS sagen, das es entweder die referenzierten Datensätze automatisch löschen oder aktualisieren darf. Damit ist zwar die Integrität weiterhin gewährleistet, jedoch bekommt der Anwender nichts davon mit und wird auch nicht durch eine Fehlermeldung darüber informiert. Deswegen auch der Hinweis von Darkservant

... kann es automatisch "motzen" ...

 

[Eagle-PsyX-]

An der Stelle bedanke ich mich mal bei euch für diesen Informationsfluß und den Disput hier

 

[PW-toXic]

wenn du einen foreign key definiert hast ohne on delete cascade, update oder set null, dann bekommst du einen Fehler wenn du einen mit foreign key referenzierten wert löscht, so dass deine ganze transaktion zurückgesetzt wird, wenn die transaktion ohne eine manuelle korrektur der refferentiellen integrität committed wird.

Die Datenbank "motzt" also sehr wohl!

 

[Rossibaer]

Toxic: Ist das nicht genau das, was ich schrieb???

ForeignKey ohne ON DELETE CASCADE -> Fehlermeldung
ForeignKey mit ON DELETE CASCADE -> keine Fehlermeldung, Daten werden kaskadierend gelöscht

ForeignKey ohne ON UPDATE CASCADE -> Fehlermeldung
ForeignKey mit ON UPDATE CASCADE -> keine Fehlermeldung, Daten werden kaskadierend aktualisiert

In jedem Fall ist die Integrität der Daten gewährleistet. Deswegen habe ich auch nochmal auf die semantische Feinheit "... kann motzen ..." hingewiesen.

Ich wünsche Dir ein schönes Wochenende...