MySQL Join einer 1:n Beziehung mit LIMIT

[klausschreiber]

Hallo,

ich habe zwei Tabellen, die ich per JOIN zusammenbringe. In der ersten Tabelle befinden sich Wrapper für jeweils mehrere Einträge der zweiten Tabelle. Also auf Deutsch, Tabelle 1 beinhaltet Briefumschläge, wobei in jeden Umschlag immer mehrere Briefe (beliebig viele) aus Tabelle 2 reinkommen.

Nun möchte ich die Anzahl der Datensätze limitieren, wobei trotzdem immer alle Datensätze eines Wrappers ausgegeben werden sollen. Also ich möchte nicht, dass z.B. die Datensätze von Wrapper 1, 2 und 3 ausgegeben werden, aber vom Wrapper 3 nur ein Teil der Datensätze.

Am besten wäre es wohl, wenn man die Anzahl der Wrapper beschränken könnte.

Mein erster Versuch war ein Subselect mittels "WHERE IN (... LIMIT 0, 10)", aber da kam die Meldung, dass LIMIT innerhalb IN nicht unterstützt wird.
Andere Versuche sind leider auch kläglich gescheitert.

Weiß jemand einen Weg? Meine bisherige Query sieht stark vereinfacht so aus:

SELECT wrappers.wrapper_id, letters.lettertext
FROM wrappers
JOIN letters
USING (wrappers_wrapper_id)

Je nachdem kommen dann noch WHERE, ORDER BY oder auch ein Subselect vor, aber das sollte ja am Prinzip nichts ändern.

Danke und Gruß,
Klaus

 

[theHacker]

Mein erster Versuch war ein Subselect mittels "WHERE IN (... LIMIT 0, 10)", aber da kam die Meldung, dass LIMIT innerhalb IN nicht unterstützt wird.

Du kannst aber ja einen Subselect "WHERE ... IN (SELECT ... LIMIT 0, 10)" machen.
Wobei ich nicht verstehe, wie das weiterhilft.

Wenn ich dein Problem recht verstehe, hast du keine konstante Anzahl an gewünschten Datensätzen, da es ja immer drauf ankommt, wie viele Briefe jetzt in den jeweiligen Umschlägen drin is.

Ich würde entweder ne konstante Anzahl an Umschlägen abrufen oder alternativ ein bisschen mehr Briefe abrufen, sodass du (im Beispiel) noch Teile von Wrapper 4 kriegst und du die nachträglich noch wegschmeißen. Letzteres bedingt allerdings, dass du ungefähr abschätzen kannst, wie sich die Anzahlen von Umschläge und Briefe verhalten.

 

[klausschreiber]

danke für deine Antwort.

ja, eine gewünschte Anzahl an Datensätzen habe ich nicht.
Also bei mir kommt bei einem Subselect mit LIMIT in mySQL5, ausgeführt über phpMyAdmin folgende Fehlermeldung

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Subquery schaut vereinfacht folgendermaßen aus:

SELECT lettertext FROM letters
WHERE wrapper_id
IN (SELECT  wrapper_id FROM wrappers LIMIT 0, 10)

Mein Plan war in der Subquery eine konstante Anzahl Briefumschläge abzurufen, um dazu dann die passenden Datensätze zu erhalten. Sehe aber gerade, ich hätte trotzdem noch einen JOIN gebraucht hätte, um an die Daten vom Briefumschalg zu kommen, aber funktioniert ja eh nicht. Sehe auch gerade, dass es auch in der Doku steht, dass LIMIT nicht in allen Subqueries unterstützt wird (https://dev.mysql.com/doc/refman/5.0/en/subquery-restrictions.html)

Ich würde entweder ne konstante Anzahl an Umschlägen abrufen

Das wäre eben auch mein Wunsch, aber mein obiger Versuch scheiterte halt leider.

oder alternativ ein bisschen mehr Briefe abrufen, sodass du (im Beispiel) noch Teile von Wrapper 4 kriegst und du die nachträglich noch wegschmeißen. Letzteres bedingt allerdings, dass du ungefähr abschätzen kannst, wie sich die Anzahlen von Umschläge und Briefe verhalten.

Daran habe ich zwar auch schon gedacht, aber es wäre halt nur eine Notlösung.

 

[theHacker]

Ah, da war was Hier is die Lösung:
https://www.klamm.de/forum/showthread.php?t=326872

 

[yoshi]

SELECT wrappers.wrapper_id, letters.lettertext 
FROM letters, (SELECT * FROM wrappers W1 LIMIT 0,10) W2 
WHERE letters.wrappers_wrapper_id = W2.wrapper_id

 

[klausschreiber]

danke für die Antworten

@yoshi:
Funktioniert einwandfrei, auch wenn ich nicht ganz verstehe, warum. Ich muss allerdings, damit ich aus beiden Tabellen (also auch z.B. wrapper_id) Daten abfragen kann, noch einen Join hinzufügen.

SELECT wrappers.wrapper_id, letters.lettertext
FROM letters
JOIN wrappers
USING (wrapper_id),
(SELECT * FROM wrappers W1 LIMIT 0,10) W2 
WHERE letters.wrappers_wrapper_id = W2.wrapper_id

Dann habe ich noch eine Frage, der Subselect sieht ja folgendermaßen aus

SELECT * FROM wrappers W1 LIMIT 0,10

Warum legst du da für wrappers den Alias W1 fest? Der kann doch weg, da er gar nicht benutzt wird, oder?

@theHacker:
Das in zwei Abfragen in Kombination mit PHP zu machen, wäre natürlich auch eine Möglichkeit, aber eleganter wäre eine Lösung direkt in SQL. DIe Lösung von ice-breaker habe ich ausprobiert, aber die hat leider nicht richtig funktioniert. Vielleicht habe auch ich etwas falsch gemacht, aber wenn ich z.B. 1 Ergebnis will, bekomme ich nichts, bei 3 Ergebnissen bekomme ich 1 Ergebnis usw.

 

[theHacker]

Funktioniert einwandfrei, auch wenn ich nicht ganz verstehe, warum.

yoshi's Abfrage verlegt den IN-Ausdruck in die FROM-Klausel, während dein und mein vorheriger Versuch ihn in der WHERE-Klausel hatten.

DIe Lösung von ice-breaker habe ich ausprobiert, aber die hat leider nicht richtig funktioniert. Vielleicht habe auch ich etwas falsch gemacht, aber wenn ich z.B. 1 Ergebnis will, bekomme ich nichts, bei 3 Ergebnissen bekomme ich 1 Ergebnis usw.

Funktioniert der innere SELECT denn ordnungsgemäß?

 

[klausschreiber]

die innere Query funktioniert. Ich habe jetzt jedoch mal ein wenig weiter getestet. Wenn ich die gesamte Query ohne Join benutze, also folgendes

SET @count = 0;
SELECT letters.lettertext
FROM letters
WHERE letters.wrapper_id IN (
SELECT wrapper_id
  FROM wrappers
  WHERE (@count:=@count+1) < 6
)

Dann kommen 5 Ergebnisse zurück, was theoretisch ja passen würde. Nur sollten halt eigentlich die passenden Briefe zu den 5 Umschlägen zurückkommen, statt dessen kommen aber genau 5 Briefe zurück (obwohl es eigentlich mehr Briefe wären und egal, ob bei einem Umschlag noch ein Brief fehlt).
Wenn ich dann noch den JOIN hinzufüge, kann ich würfeln, wie viel Ergebnisse wohl kommen werden (wobei es fast so aussieht, als würde genau die Hälfte der gewünschten Ergebnisse zurückgegeben, aber halt wieder für die letters, nicht für die wrappers)

Aber ich vermute mal, die Abfrage von yoshi ist ähnlich gut/performant, oder? Dann wäre es ja nicht schlimm, dass ice-breakers Abfrage bei mir nicht funktioniert, auch wenn ich es komisch finde, weil seine Abfrage eigentlich logisch aussieht.

Gruß,
Klaus

 

[ice-breaker]

mein Query ist nicht effizient, steht doch auch im Post, es ging mir nur damals darum zu zeigen, dass es auch mit nur einem Query geht.

yoshi's Query lese ich mit gemischten Gefühlen, der Subselect auf den gejoint wird, wird zu einer temporären Tabelle (schonmal nicht so optimal), wenn MySQL sich mit dem joinen da verschätzen sollte, könnte das auch arg schief gehen.

Die Lösung den String für das IN()-Konstrukt in PHP zusammenzubasteln ist immernoch der sicherste Weg, mache ich auch.

 

[klausschreiber]

also können da auch unerwartete Ergebnisse rauskommen oder kann es zu nicht vorauszusehenden Performanceproblemen kommen? Ich dachte halt eigentlich, dass es schneller ist, wenn alles in einer Abfrage erledigt wird, als wenn erst das Ergebnis zurück zu PHP geleitet wird und dann nochmal eine neue Abfrage gestartet wird.

Gruß,
Klaus

 

[ice-breaker]

also können da auch unerwartete Ergebnisse rauskommen oder kann es zu nicht vorauszusehenden Performanceproblemen kommen?

bei nem Join mit einer temporären Tabelle würde ich eben nicht meine Hand ins Feuer legen, da gehe ich lieber den sicheren Weg über PHP.
Denn mit vielen Daten (wirklich vielen) kann ein schlechter QEP (Query Exceution Plan) auch schonmal im Sekundenbereich liegen, und alles in PHP zu verketten braucht sicherlich keine Sekunde.
Zumal ich mit der PHP-Lösung einen deutlich simpleren Query habe, ich habe mir einen Join gespart.

 

[klausschreiber]

jo, wenn ich es komplett in PHP zusammenfriemle, habe ich sogar zwei Joins gespart, war nur etwas verunsichert weil in einem anderem Fall (allerdings mit viel mehr Abfragen) der Geschwindigkeitsunterschied enorm war. Dann werde ich es in Abfragen machen und bedanke mich mal wieder für die Hilfe von euch.

 

[yoshi]

Friemle nichts in PHP zusammen, sondern versuche immer, alles in einer SQL Anfrage auszudrücken, dann kann das Datenbanksystem die Anfrage optimieren.

Wieso ich den Alias W1 verwendet hab? Einfach so. Lieber zu oft umbenannt, als einmal zu wenig.

Wenn du nicht weiter kommst, bau dir doch eine View. Erstelle eine View, in der du auch das Limit verwenden kannst. CREATE VIEW meineView AS SELECT ..... LIMIT 0,10. Was ich gemacht hab, ist im Prinzip das benutzen einer View, ohne sie zu erstellen. Was macht das Datenbanksystem, wenn es eine Anfrage in einer View empfängt (SELECT ... FROM meineView ...), der AS-Teil aus der View-Definition wird einfach an diese Stelle gesetzt.

 

[Sebmaster]

Friemle nichts in PHP zusammen, sondern versuche immer, alles in einer SQL Anfrage auszudrücken, dann kann das Datenbanksystem die Anfrage optimieren.

Auch DB-System sind nicht perfekt, und kaputtoptimieren manchmal

 

[klausschreiber]

danke für eure Antworten und die Erklärung, wie diese SQL Abfrage funktioniert. Problem ist jetzt natürlich, dass ich zwei gegenteilige Aussagen habe und ich dadurch nicht weiß, nach Welcher ich mich richten soll. Werde es wohl jetzt erstmal so lassen, da ich es inzwischen bereits mittels zwei Abfragen und PHP umgesetzt habe. Da ich dadurch ja auch zwei Joins spare und die Daten sowieso per PHP in ein multidimensionales Array überführen muss, sollte es ja eigentlich, wenn überhaupt, nur minimal langsamer sein, oder?

Gruß,
Klaus

 

[ice-breaker]

Friemle nichts in PHP zusammen, sondern versuche immer, alles in einer SQL Anfrage auszudrücken, dann kann das Datenbanksystem die Anfrage optimieren.

du weisst aber schon, dass wir von MySQL reden?

Was macht das Datenbanksystem, wenn es eine Anfrage in einer View empfängt (SELECT ... FROM meineView ...), der AS-Teil aus der View-Definition wird einfach an diese Stelle gesetzt.

les dir bitte nochmal durch, was Views sind und wie sie funktionieren
Denn das "einsetzen an die Stelle" ist nur Theorie, praktisch sieht es anders aus.
CREATE VIEW, besondes den Teil zum "ALGORITHM" lesen.

Du hast nämlich keinen View erstellt, sondern eine temporäre Tabelle, ein himmelweiter Unterschied. Es wird erst eine Tabelle im Arbeitsspeicher erzeugt, dort deine Daten inserted und dann auf diese Tabelle gejoint.

Und das Zusammenspiel mit Views wird in den meisten Fällen (Grouping-Functions) auch deswegen zu temporären Tabellen und keinem Umschreiben des Querys.