SQL Mysql Abfrage performant / richtiger Ansatz ?

[Schwenni]

Hallo zusammen,

ich bin mir grad nicht sicher ob mein Ansatz der richtige ist oder ob es vielleicht besser zu lösen geht (performanter)

Ausgangssituation (Beispieldaten)

Tabelle A - Personen - (Vorname, Name etc. + ID's für Wohnort und Bundesland)
Tabelle B - Meldungen - (Nachrichten der Personen - also eine Tabelle mit N:1 Beziehung zu Tabelle A)
Tabelle C - Historie - (Vorgänge zu den Personen - also auch eine Tabelle mit N:1 Beziehung zu Tabelle A)

Die Anzahl der Datensätze ist ca. so verteilt: A / B / C -> 1000 / 1.000.000 / 2.500.000

Ziel ist es jetzt zu allen Personen in einem bestimmten Bundesland (1) aus Tabelle B zwei bestimmten Meldung abzufragen und aus Tabelle C diverse Vorgänge (Vorgang A - x mal, Vorgang B - x mal, Vorgang C - x mal) zu zählen

Ich würde jetzt folgenden Ansatz wählen:

select person from a where bundesland = 1

Da ich 2 Felder aus Tabelle B und 3 aus Tabelle C brauch und die Tabellen in N:1 Beziehung stehen komm ich hier mit Left Join nicht weiter richtig?
Ich würde demnach die Abfragen in Sub Selects packen was aber in meinen Augen nicht performant ist da er ja für jede Personen ID immer über die kompletten Tabellen B und C rattern muss. Zumal die Daten von C ja jedesmal gruppiert werden müssen zum zählen.

select a.person
, (select b.meldung from B where a.id = b.id and meldung like "YYY") as meldung1
, (select b.meldung from B where a.id = b.id and meldung like "XXX") as meldung2
, (select count(c.id) from C where a.id = c.id and c.vorgang = '1' group by c.id) as anz_vorgang1
, (select count(c.id) from C where a.id = c.id and c.vorgang = '2' group by c.id) as anz_vorgang2
, (select count(c.id) from C where a.id = c.id and c.vorgang = '3' group by c.id) as anz_vorgang3
from a where bundesland = 1


Oder doch mit Left join auf C mit group by c.id um dann mit einer SUM IF Abfrage die Vorgänge zu zählen?

select a.person
, (select b.meldung from B where a.id = b.id and meldung like "YYY") as meldung1
, (select b.meldung from B where a.id = b.id and meldung like "XXX") as meldung2
, SUM (if c.vorgang = 1, 1, 0) as anz_vorgang1
, SUM (if c.vorgang = 2, 1, 0) as anz_vorgang2
, SUM (if c.vorgang = 3, 1, 0) as anz_vorgang3
from a
left join C on a.id = c.id
where bundesland = 1
group by C.id

Kann man hier die 2 Sub Selects noch anders (besser) auflösen=? Oder habt ihr gar ganz andere Vorschläge wie das besser zu handhaben ist ?

Danke für Eure Tipps und Hinweise.

PS: Das ist nur Beispiel Code und ist nicht getestet.

 

[NullPointer]

Das geht schon auch mit Left Joins, etwa so:

select a.person, b1.meldung as meldung1, b2.meldung as meldung2, count(c1.id) as anz_vorgang1, count(c2.id) as anz_vorgang2, count(c3.id) as anz_vorgang3
from a
left join b b1 on a.id = b1.id and b1.meldung like "YYY"
left join b b2 on a.id = b2.id and b2.meldung like "XXX"
left join c c1 on a.id = c1.id and c1.vorgang = "1"
left join c c2 on a.id = c2.id and c2.vorgang = "2"
left join c c3 on a.id = c3.id and c3.vorgang = "3"
where a.bundesland = 1
group by a.id

Ob es performant(er) ist, können wir dir aber ohne Kenntnis deines Schemas nicht sagen. Probier am besten beide Optionen aus und benutze EXPLAIN, um zu sehen, welche Indizes benutzt werden. Aus dem Bauch heraus würde ich vermuten, dass das Vorhandensein geeigneter Indizes deutlich mehr Auswirkungen auf die Performance hat als die Entscheidung zwischen Join und Sub-Select. Am kritischsten sieht für mich hier die Verwendung von LIKE aus, wenn man das unvorsichtig benutzt, hat man schnell einen Full Table Scan am Hals. Lesetipp: https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

Nachtrag: Je nachdem, wie flexibel du an dieser Stelle bist, lohnt es sich evtl., die Abfrage in mehrere aufzuteilen. Statt dreimal die Tabelle c anzusprechen, könntest du dich auf einmal beschränken, wenn du es so aufziehst:

select c.id, c.vorgang, count(c.id) as anzahl
from c
join a on c.id = a.id
where c.vorgang in (1, 2, 3)
and a.bundesland = 1
group by c.id, c.vorgang

 

[Schwenni]

Danke für deinen Tipp ich werde das heute abend mal lesen und etwas rumspielen und berichten

 

[RalphS]

Nur mal kurz überflogen, sorry falls ich was mißverstanden hab:

• subselect ist erstmal kein Problem
• aber die im Op oben sind arg korreliert. DAS ist ein Problem.

Deshalb die Abfrage abflachen, zb mit joins wie bereits erwähnt. Korrelationen gilt es zu meiden wie die Pest, außer es ist schlicht und ergreifend nicht anders möglich, was aber nicht der Fall sein dürfte.

Von Korrelationen abgesehen ist es einem guten dbms egal wie die Abfrage aussieht. Ob da MySQL zugehört, meh. Explains/Describes sagen aber auch dort, wo ein Index gut ist.

Protip. Je weniger Daten, desto besser. Gestalte die Abfrage so, das bei den kleinsten Tabellen angefangen wird. 1 mio records anfassen und alle außer 15 wegwerfen ist albern.

 

[Schwenni]

das mit den left joins klappt so nicht... er zählt dann immer mehrfach

Beispiel wenn ich nur die left joins der Vorgänge nutze:
Ich habe 3 * Vorgang 1 und jeweils 1 * Vorgang 2 und 3 (3,1,1)

Er zählt dann aber mehrmals und zwar so oft wie die Anzahl in Vorgang 1 ist-... also 3 mal und es kommt dann 3,3,3 raus.

Wenn ich jetzt noch die left joins der mitteilungen dazu nehme dann zählt er das ganze * die anzahl der mitteilungen je person.

In dem Beispiel kommt dann raus 6,6,6.

Wo ich wieder bei meiner Ausgangs SQL bin mit den 2 Subselects:

select a.person
, (select b.meldung from B where a.id = b.id and meldung like "YYY") as meldung1
, (select b.meldung from B where a.id = b.id and meldung like "XXX") as meldung2
, SUM (if c.vorgang = 1, 1, 0) as anz_vorgang1
, SUM (if c.vorgang = 2, 1, 0) as anz_vorgang2
, SUM (if c.vorgang = 3, 1, 0) as anz_vorgang3
from a
left join C on a.id = c.id
where bundesland = 1
group by a.id

Ergänzung (30. Juli 2019)

aber die im Op oben sind arg korreliert. DAS ist ein Problem.

sorry aber das verstehe ich nicht

 

[mambokurt]

das mit den left joins klappt so nicht... er zählt dann immer mehrfach

Beispiel wenn ich nur die left joins der Vorgänge nutze:
Ich habe 3 * Vorgang 1 und jeweils 1 * Vorgang 2 und 3 (3,1,1)

Er zählt dann aber mehrmals und zwar so oft wie die Anzahl in Vorgang 1 ist-... also 3 mal und es kommt dann 3,3,3 raus.

Wenn ich jetzt noch die left joins der mitteilungen dazu nehme dann zählt er das ganze * die anzahl der mitteilungen je person.

In dem Beispiel kommt dann raus 6,6,6.

Wo ich wieder bei meiner Ausgangs SQL bin mit den 2 Subselects:

select a.person
, (select b.meldung from B where a.id = b.id and meldung like "YYY") as meldung1
, (select b.meldung from B where a.id = b.id and meldung like "XXX") as meldung2
, SUM (if c.vorgang = 1, 1, 0) as anz_vorgang1
, SUM (if c.vorgang = 2, 1, 0) as anz_vorgang2
, SUM (if c.vorgang = 3, 1, 0) as anz_vorgang3
from a
left join C on a.id = c.id
where bundesland = 1
group by a.id

Ergänzung (30. Juli 2019)

sorry aber das verstehe ich nicht

Subselects bei mysql immer mit Exists, und ich würde das mit nem Union machen statt mit ifsum (was schneller ist musst du aber probieren). Ansonsten poste mal wie die Tabellen aussehen und schreib auf Deutsch was du brauchst, da kann man dir eher helfen als wenn du Codeschnitzel hinwirfst

 

[RalphS]

sorry aber das verstehe ich nicht

Korrelierte Abfragen sind Abfragen, die externe Referenzen haben:

SELECT a,id, a.name 
FROM people a 
WHERE EXISTS (SELECT 1 FROM home b where a.current_home = b.id)

Das ist natürlich arg konstruiert, aber man sieht hoffentlich, daß die Unterabfrage (hier in WHERE) sich auf die äußere Abfrage bezieht. Das führt dazu, daß diese Unterabfrage nicht ein- oder ggfs. einige wenige Male ausgeführt werden wird, sondern in Abhängigkeit der Ergebnismenge der äußeren Abfrage (hier einmal pro Ergebnis in A).

Solche Konstrukte lassen sich (meistens) per einer der JOIN-Arten auflösen. Dazu fragt man sich, was man überhaupt will, versteht die beiden Abfragen als Ergebnisteilmengen und überlegt sich dann, wie man beide zusammenbauen muß, um das Richtige zu bekommen.

Ich hatte eigentlich vorgehabt, das anhand Deines Beispiels zumindest im Ansatz aufzulösen.... geht aber leider nicht, weil Personen.ID fast garantiert nicht mit Meldung.ID zusammenpassen können .

So wie ich den Fall überblicke fehlt da eine Assoziation (Person; Meldung), also [person_id; meldung_id] oder sowas in der Art.

Daher, wie bereits erwähnt, schreib am besten in Worten was Du brauchst und dann schauen wir mal, was für ein Schema wir benötigen. Wenn wir das haben, können wir die Abfrage(n) angehen.

Und schau auch mal in Richtung der möglichen JOINs, einschließlich der nicht als solchen syntaktisch verfügbaren. Von denen ist der Erfahrung nach der Antijoin der am häufigsten gebrauchte, aber das ist natürlich ein Problem der Anwendung und kann bei jedem anders aussehen.

 

[Schwenni]

Personen Tabelle A

id	name	vorname	bundesland_id	geburtsdatum
1	Mustermann	Max	1	10.01.1980
2	Musterfrau	Martina	1	12.06.1982
3	Fischer	Heidi	2	24.05.1990

Meldungen Tabelle B

id	m_pid	subject	body	create_time
1	1	Halli Hallo Halööle	Text XXX Text	03.01.2018
2	1	HI	Blahh Blahh	05.02.2018
3	1	Tach	Ich .. YYY... doch ...	13.04.2018
4	2	Huhu	Jaaa aber	06.09.2018
5	2	Hey	Hmmmm XXX ..	07.08.2019
6	3	Hui	Gnaaar	09.08.2020

Historie Tabelle C

id	h_pid	type_id	create_time
1	1	3	03.01.2018
2	1	16	05.02.2018
3	1	37	06.02.2018
4	1	10	07.02.2018
5	1	12	13.02.2018
6	1	16	03.03.2018
7	1	37	05.03.2018
8	1	16	07.03.2018
9	1	38	09.04.2018
10	2	3	03.01.2018
11	2	16	04.01.2018
12	2	38	05.01.2018
13	3	12	07.03.2019

Hier mal ein Auszug der Tabellen mit Beispiel Daten
Die Personen id ist in B und C mit m_pid und h_pid geschlüsselt.
Die Vorgangs ID in C mit type_id.


Gewünschtes Ergebnis

alle Personen mit bundesland_id 1 (p_id)
die create_time der zur Person gehörigen Meldung (Tabelle B) wo in der Spalte Body XXX enthalten ist (m1)
die create_time der zur Person gehörigen Meldung (Tabelle B) wo in der Spalte Body YYY enthalten is, (m2)
die zur Person gehörige Anzahl der Vorgänge (Tabelle C) mit der type_id 16 (v1)
die zur Person gehörige Anzahl der Vorgänge (Tabelle C) mit der type_id 37 (v2)
die zur Person gehörige Anzahl der Vorgänge (Tabelle C) mit der type_id 38 (v3)

p_id	m1	m2	v1	v2	v3
1	03.01.2018	13.04.2018	3	2	1
2	07.08.2019		1	0	1

Ich hoffe das ist jetzt verständlich.
mfG
Sven

 

[ayngush]

Gnaaar

Ich beginne meine Nachrichten jetzt auch nur noch mit "Gnaaar" Sehr schön
Das gewünschte Ergebnis wirst du nur mit den Unterabfragen oder den outer Joins aus diesem Datenmodell zustande bringen. Beides sind nicht gerade Performance-Wunder.

Ist das Ergebnis in der Form in ein einziges Query formuliert unbedingt notwendig oder könnte man das auch auf mehrere Querys aufteilen und dem Programm die restliche Arbeit überlassen? Oder mit temporären Zwischenergebnissen arbeiten?

Das Problem ist, dass du eine Pivot-Tabelle haben möchtest.
Nein, anders formuliert: Das Problem ist, dass du eine performante Pivot-Tabelle haben möchtest.
Das ist immer ein Problem. Es gibt moderne Lösungsansätze dafür.
Leider ist mysql / mariadb an der Stelle nicht modern und unterstützt da nichts.

 

[mambokurt]

Personen Tabelle A

id	name	vorname	bundesland_id	geburtsdatum
1	Mustermann	Max	1	10.01.1980
2	Musterfrau	Martina	1	12.06.1982
3	Fischer	Heidi	2	24.05.1990

Meldungen Tabelle B

id	m_pid	subject	body	create_time
1	1	Halli Hallo Halööle	Text XXX Text	03.01.2018
2	1	HI	Blahh Blahh	05.02.2018
3	1	Tach	Ich .. YYY... doch ...	13.04.2018
4	2	Huhu	Jaaa aber	06.09.2018
5	2	Hey	Hmmmm XXX ..	07.08.2019
6	3	Hui	Gnaaar	09.08.2020

Historie Tabelle C

id	h_pid	type_id	create_time
1	1	3	03.01.2018
2	1	16	05.02.2018
3	1	37	06.02.2018
4	1	10	07.02.2018
5	1	12	13.02.2018
6	1	16	03.03.2018
7	1	37	05.03.2018
8	1	16	07.03.2018
9	1	38	09.04.2018
10	2	3	03.01.2018
11	2	16	04.01.2018
12	2	38	05.01.2018
13	3	12	07.03.2019

Hier mal ein Auszug der Tabellen mit Beispiel Daten
Die Personen id ist in B und C mit m_pid und h_pid geschlüsselt.
Die Vorgangs ID in C mit type_id.


Gewünschtes Ergebnis

alle Personen mit bundesland_id 1 (p_id)
die create_time der zur Person gehörigen Meldung (Tabelle B) wo in der Spalte Body XXX enthalten ist (m1)
die create_time der zur Person gehörigen Meldung (Tabelle B) wo in der Spalte Body YYY enthalten is, (m2)
die zur Person gehörige Anzahl der Vorgänge (Tabelle C) mit der type_id 16 (v1)
die zur Person gehörige Anzahl der Vorgänge (Tabelle C) mit der type_id 37 (v2)
die zur Person gehörige Anzahl der Vorgänge (Tabelle C) mit der type_id 38 (v3)

p_id	m1	m2	v1	v2	v3
1	03.01.2018	13.04.2018	3	2	1
2	07.08.2019		1	0	1

Ich hoffe das ist jetzt verständlich.
mfG
Sven

Unschön, liest sich wie ein Haufen Subselects. Wenns zu heftig wird würde ich das denormalisieren und eine eigene Tabelle anlegen oder wie bereits angemerkt die Daten separat ziehen und im Programm zusammensetzen... Das lässt sich anders nicht wirklich lösen.

 

[RalphS]

Wenn ich mir die Beschreibung der Ergebnismenge so anschaue... ist das wirklich eine Ergebnismenge, oder sind es mehrere? Oder anders, sind es ale Personen aus Bundesland 1, die die folgenden Eigenschaften erfüllen, oder hat das nichts miteinander zu tun?

Wenn, wie ich annehme, das verschiedene Ergebnismengen sind, dann wäre imo der passendste Weg, jede Ergebnismenge individuell zu bestimmen. Wenn man's wirklich drauf ankommen läßt, kann man ggfs. jeweils ein Subselect per Ergebnismenge zimmern und die dann per JOIN zusammenbauen, aber ob MySQL da mitspielt und ob das dann so schön wäre.... meh.

Das würde überdies auch die Tabelle in der Darstellung völlig konfus machen, wenn jede Spalte etwas anderes ausdrücken will und die eine nach etwas gefiltert ist und die andere nach etwas anderem und die nächste wiederum gar nicht. Evtl muß man sich dann auch in der Darstellung was einfallen lassen. Wenn das Ganze zB als Website ausgegeben werden soll, dann kann man einzelne Elemente durchaus auch anklickbar machen und zugehörige, aber unabhängige Daten in einem eigenen Bereich öffnen.

Drei unabhängige Komponenten scheinst Du ja zu haben in der gesuchten Ergebnismenge. Wobei ich mich ehrlich frage, warum m1 und m2 nebeneinanderstehen müssen... rein intuitiv ist das doch eher eine Spalte? Na, vielleicht steh ich aber auch nur auf dem Schlauch.


Weil es für mich allerdings grad unterschwellig den Eindruck macht, an der Stelle zur Sicherheit: Bitte NICHT den Fehler begehen und ein DBMS mit Excel verwechseln. Die von der Abfrage gelieferten Daten müssen NICHT anschaulich schön in einer zweidimensionalen Matrix vorliegen. Man kann die v1, v2 und v3 zB auch einfach mit SELECT ... FROM ... WHERE type_id IN (16,37,38) einsammeln, dann steht das zwar nicht nebeneinander, aber die Darstellung gehört eh ins Frontend und dann muß man das dort halt zB mit einem Switch oder sowas nachbereiten. Klar, Pivot wäre eine Option, sicher, aber wie gesagt, auch Pivot hat nicht die Aufgabe, die Ergebnismenge einer Abfrage anschaulicher zu gestalten.


Oder anders, es besteht kein besonderer Grund, nicht-Zusammengehöriges doch zusammenzuwerfen, und selbst wenn es zusammengehören sollte, so kann man insbesondere einzelne Komponenten auch unabhängig betrachten und unabhängig verarbeiten.

Sonst hat man am Ende einen Koloß aus X Zeilen Code, der sich noch nicht mal für einen VIEW eignet und der per Ausführung dann erstmal das DBMS für eine Weile lahmlegt. Da hätte man auch nichts gekonnt.

 

[Schwenni]

Nabend...

also ich hab es aktuell noch so und es funktioniert relativ gut / performant

select a.person
, (select b.meldung from B where a.id = b.p_id and meldung like "YYY") as meldung1
, (select b.meldung from B where a.id = b.p_id and meldung like "XXX") as meldung2
, SUM (if c.vorgang = 1, 1, 0) as anz_vorgang1
, SUM (if c.vorgang = 2, 1, 0) as anz_vorgang2
, SUM (if c.vorgang = 3, 1, 0) as anz_vorgang3
from a
left join C on a.id = c.p_id
where bundesland = 1
group by a.id


Und das ganze ist eine Abfrage in PowerPivot als eine Ergebnismenge.
Passt mir lieber als mehrere Abfragen verteilt was aber auch kein Problem wär.
Daraus sollen halt diverse Diagramme entstehen. (das zum Hintergrund)

Mir ging es ja hauptsächlich darum Feedback zu erhalten was ich verbessern kann oder ob mein Ansatz Mist ist.

Aktuell dauert die Abfrage im Schnitt 0.0035 Sek.

 

[ayngush]

Die Abfragedauer ist doch für Datenauswertungen vollkommen in Ordnung.

 

[GustlPC]

Nabend...

also ich hab es aktuell noch so und es funktioniert relativ gut / performant

select a.person
, (select b.meldung from B where a.id = b.p_id and meldung like "YYY") as meldung1
, (select b.meldung from B where a.id = b.p_id and meldung like "XXX") as meldung2
, SUM (if c.vorgang = 1, 1, 0) as anz_vorgang1
, SUM (if c.vorgang = 2, 1, 0) as anz_vorgang2
, SUM (if c.vorgang = 3, 1, 0) as anz_vorgang3
from a
left join C on a.id = c.p_id
where bundesland = 1
group by a.id


Und das ganze ist eine Abfrage in PowerPivot als eine Ergebnismenge.
Passt mir lieber als mehrere Abfragen verteilt was aber auch kein Problem wär.
Daraus sollen halt diverse Diagramme entstehen. (das zum Hintergrund)

Mir ging es ja hauptsächlich darum Feedback zu erhalten was ich verbessern kann oder ob mein Ansatz Mist ist.

Aktuell dauert die Abfrage im Schnitt 0.0035 Sek.

Ich denke mit den like Abfragen wirst du bei größeren Mengen an Datensätze länger brauchen. Erzeuge doch mal 3mio. Meldungen in der Datenbank und auch 2000 personen. Dann lass die Abfrage nocheinmal laufen.

Dann kannst du mit sogenannten Indexes optimieren.
https://dev.mysql.com/doc/refman/5.5/en/optimization-indexes.html

 

[Schwenni]

Gibt es irgendwo ne Spielwiese wo man sowas nachstellen könnte ? - Tabellen anlegen und Dummydaten erzeugen etc. (analog https://codepen.io/ für html, css, js etc)
Hab keine Schreibrechte mit dem DB Account.

 

[GustlPC]

Nicht das ich wüsste... ich hätte da ein einfach script geschrieben welches mir zufällig die Tabellen befüllt.

 

[mambokurt]

Gibt es irgendwo ne Spielwiese wo man sowas nachstellen könnte ? - Tabellen anlegen und Dummydaten erzeugen etc. (analog https://codepen.io/ für html, css, js etc)
Hab keine Schreibrechte mit dem DB Account.

http://www.sqlfiddle.com

 

[parats]

Vorsicht bei der Anlage von Indizes, viele propagieren hier gerne "viel hilft viel", abhängig von der Datenbank Engine kann eine heap table schneller die Ergebnisse liefern als über einen Index mit dem entsprechenden Overhead. Auch mehrere Indizes die über den natürlichen Schlüssel hinausgehen behindern sich ggf. gegenseitig.

 

[RalphS]

Das sind korrelierte Abfragen, da hilft kein Index der Welt etwas.

Ansonsten hat @parats natürlich völlig recht. Viel hilft NICHT viel. Ganz im Gegenteil erschweren Indices das Einfügen neuer Daten, und je mehr Indices da sind und je mehr Daten da sind, desto problematischer das Ganze.

Meine Sache ist die Abfrageform nicht (eine Abfrage für verschiedene Ergebnismengen) aber ist ja auch nicht meine Angelegenheit.

 

[GustlPC]

Ja, es ist eine korrelierte Abfrage, aber in der Unterabfrage hat er auch ein LIKE drin welches nichts mit der übergeordneten Anweisung zu tun hat. Warum ratet ihr von Indizies denn ab wenn sie doch bei den Abfragen auch Performanceschub bringen können?

Auf jede Spalte ein Index zu setzen hilft reichlich wenig, das stimmt. Aber wenn z.B. in der Spalte subject Wörter gesucht werden sollen hilft ein fulltext index auf jeden Fall.

Hier hat wer mal einen Test gemacht: Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search