MySQL - Query Statement gesucht

[Mike©]

Moin alle,

ich suche verzweifelt nach einem Select Statement:
Tabellenname = berichte

Spalten:
id, text, entry_timestamp, mannschaft, newsletter

Ich brauche eine Abfrage welche mir den neuesten Eintrag (max entry_timestamp) pro Mannschaft (group by mannschaft) anzeigt, WHERE newsletter='N'

Das hier funktioniert ja leider so nicht:
[...]from berichte WHERE max(entry_timestamp)[...]

Danke & regds
Mike©

[Vinzenz Mai]

Hallo Mike,

das ist eine häufig gestellte Frage. Die Antwort, ob es einfach möglich ist, hängt von Deiner MySQL-Version ab. Bei MySQL ist es wegen der enormen Unterschiede im Funktionalitätsumfang zwischen den einzelnen Versionen immer sinnvoll, bei einer Frage hier im Forum die verwendete Version anzugeben

Tabellenname = berichte
Spalten:
id, text, entry_timestamp, mannschaft, newsletter

Ich brauche eine Abfrage welche mir den neuesten Eintrag (max entry_timestamp) pro Mannschaft (group by mannschaft) anzeigt, WHERE newsletter='N'

Du benötigst eine korrelierte Unterabfrage, Unterabfragen werden von MySQL ab der Version 4.1 unterstützt. Du könntest die Lösung in </archiv/2006/1/t122127/#m785397> an Dein Problem anpassen.

Ist Deine MySQL-Version älter, dann wird es gemeiner und mit einem sowohl häßlichen wie ineffizienten Trick lösbar, siehe Archiv. Poste deswegen bitte Deine Version.

Freundliche Grüße

Vinzenz

[Mike©]

Moin Vinzenz,

Poste deswegen bitte Deine Version.

hier die Version: MySQL 4.1.12a-nt

Ein Notnagel wäre ja das hier:

select max(entry_timestamp) as latest from berichte where newsletter='N' group by mannschaft

und anhand der Ergebnissmenge eine weiteres select:

select text from berichte where entry_timestamp = ergebniss_erstes_query

Vorausgesetzt die Timestamps sind eindeutig.

regds
Mike©

[Mike©]

Moin Vinzenz,

ich habe es so gelöst:

  
  
$query = "select max(entry_timestamp) as latest from berichte WHERE newsletter='N' group by mannschaft";  
$result = mysql_query($query, $db_pointer);  
  
while ($liste=mysql_fetch_array($result) )  
      {  
 $latest=$liste['latest'];  
  
 $query1="select header from berichte WHERE entry_timestamp=$latest";  
 $result1 = mysql_query($query1, $db_pointer);  
 $liste1=mysql_fetch_array($result1);  
  
 $Header=$liste1['header'];  
  
 echo '  
 '.$Header.'  
 <br>';  
      }  

regds
Mike©

[Vinzenz Mai]

Hallo Mike,

Warum?

Du kannst doch das Subselect verwenden.
[... überflüssiger PHP-Code ...]

Freundliche Grüße

Vinzenz

[wahsaga]

hi,

abgesehen davon, dass wiederholtes selektieren in Schleifen wenn möglich zu vermeiden ist -

$query1="select header from berichte WHERE entry_timestamp=$latest";

• ist damit überhaupt sichergestellt, dass du den richtigen Datensatz holst?

Was, wenn es mehrere Datensätze, ggf. auch aus unterschiedlichen Kategorien (Mannschaften) gibt, die den gleichen Zeitstempel haben?

Wenn du schon so suboptimal vorgehen willst, dann nutze doch wenigstens die ID des Datensatzes für die eindeutige Auswahl.

gruß,
wahsaga

[Mike©]

Moin wahsaga,

• ist damit überhaupt sichergestellt, dass du den richtigen Datensatz holst?

Was, wenn es mehrere Datensätze, ggf. auch aus unterschiedlichen Kategorien (Mannschaften) gibt, die den gleichen Zeitstempel haben?

99,9 % sicher

Wenn du schon so suboptimal vorgehen willst, dann nutze doch wenigstens die ID des Datensatzes für die eindeutige Auswahl.

Gerne, wenn Du mir verrätst, wie ich "ersten" query die ID hole.

$query = "select max(entry_timestamp) as latest from berichte WHERE newsletter='N' group by mannschaft";

regds
Mike©

[wahsaga]

hi,

Wenn du schon so suboptimal vorgehen willst, dann nutze doch wenigstens die ID des Datensatzes für die eindeutige Auswahl.

Gerne, wenn Du mir verrätst, wie ich "ersten" query die ID hole.

Mit der Query? Ich wüsste nicht, wie.

Mit einer Subquery, wie von Vinzenz vorgeschlagen, sollte das schon eher möglich sein.
Dabei würdest du nicht GROUPpieren und MAX verwenden, um den aktuellsten Datensatz zu erhalten, sondern ORDER BY und LIMIT.

gruß,
wahsaga

[Vinzenz Mai]

Hallo wahsaga,

Mit der Query? Ich wüsste nicht, wie.

Mit einer Subquery, wie von Vinzenz vorgeschlagen, sollte das schon eher möglich sein.

noch besser in </archiv/2006/3/t124769/#m804136> nachlesbar. Das sollte fast 1:1 umsetzbar sein.

Dabei würdest du nicht GROUPpieren und MAX verwenden, um den aktuellsten Datensatz zu erhalten, sondern ORDER BY und LIMIT.

Wie ich im gleichen Archivthread dargelegt habe, sind weder ORDER BY noch LIMIT geeignet ...

Freundliche Grüße

Vinzenz

[wahsaga]

hi,

Wie ich im gleichen Archivthread dargelegt habe, sind weder ORDER BY noch LIMIT geeignet ...

Warum nicht?

Ob ich in der Subquery den Datensatz mit dem aktuellsten Zeitstempel aus einer mittels WHERE-Klausel eingeschränkten Menge mittels MAX oder über ORDER BY und LIMIT ermittle - wo liegt der Unterschied? (Wenn wir von möglichen Performanceunterschieden mal absehen.)

gruß,
wahsaga

[Ilja]

yo,

Wie ich im gleichen Archivthread dargelegt habe, sind weder ORDER BY noch LIMIT geeignet ...

Warum nicht?

des rästels lösung liegt an der aufgabenstellung oder besser gesagt in den beiden worten "pro manschaft". und genau da fangen dann die schwierigkeiten mit ORDER BY und LIMIT an. damit bekommt man keinen bezug zu den manschaften hin, eine korrelierende unterabfrage macht aber genau das.

Ilja

[wahsaga]

hi,

des rästels lösung liegt an der aufgabenstellung oder besser gesagt in den beiden worten "pro manschaft". und genau da fangen dann die schwierigkeiten mit ORDER BY und LIMIT an. damit bekommt man keinen bezug zu den manschaften hin, eine korrelierende unterabfrage macht aber genau das.

Ich rede doch von einem Subselect, in Kombination mit ORDER BY und LIMIT 1 zur Auswahl des jeweils neuesten Zeitstempels pro Mannschaft - wieso soll ich da keinen Bezug zur Mannschaft hinkommen?

  
SELECT * FROM tabelle t1  
WHERE entry_timestamp = (  
   SELECT entry_timestamp  
   FROM tabelle t2  
   WHERE t1.mannschaft = t2.mannschaft  
   ORDER BY entry_timestamp DESC  
   LIMIT 1 )

Das ist eigentlich nicht viel anders als das, was Vinzenz im von ihm verlinkten Archiv-Thread vorschlug - nur das ich hier eben nicht MAX zur Ermittlung des aktuellsten Satzes benutze, sondern ORDER BY und LIMIT.

Wie schon gesagt, ich wollte keine Aussage darüber treffen, was da performanter ist - ich glaube allerdings nicht, dass MAX vs. ORDER BY/LIMIT 1 da einen signifikanten Unterschied macht.

gruß,
wahsaga

[Ilja]

yo,

Ich rede doch von einem Subselect, in Kombination mit ORDER BY und LIMIT 1 zur Auswahl des jeweils neuesten Zeitstempels pro Mannschaft - wieso soll ich da keinen Bezug zur Mannschaft hinkommen?

ahh ok, dann machst du aber wirklich genau das, was Vinzenz vorgeschlagen hat, nämlich eine korrelierende unterabfrage. ich würde allerdings trotzdem die aggegrat-funktion MAX() verwenden, weil damit meinern meinung nach der code und dessen sinn leichter zu lesen ist. aber das ergebnis sollte in dem falle das gleiche sein.

Ilja

[dedlfix]

echo $begrüßung;

Wie schon gesagt, ich wollte keine Aussage darüber treffen, was da performanter ist

Du hast sicherlich nichts dagegen, wenn ich trotzdem dieses Thema vertiefen möchte. :-)

ich glaube allerdings nicht, dass MAX vs. ORDER BY/LIMIT 1 da einen signifikanten Unterschied macht.

Das käme, so man das wirkliche Verhalten MySQLs nicht kennt, auf einen Versuch an. Ich versuche es zunächst mit Trockenschwimmen:

(Wenn es signifikante Unterschiede gibt werden diese sicherlich erst ab einer bestimmten Datenmenge anfangen, ins Gewicht zu fallen. Bei geringen Datenmengen sollten sie in der Praxis keine Rolle spielen.)

Bei MAX() könnte ich mir zwei Arbeitsweisen vorstellen.
MAX-1: Die Tabelle wird vollständig durchlaufen. Die nicht zutreffenden Datensätze (WHERE) werden ignoriert, von den anderen merkt man sich den bisher höchsten. Kommt ein höherer merkt man sich den.

MAX-2: Zuerst wird eine WHERE-gefilterte Menge angelegt, und dann darin (mittels Durchlaufen und von einer unsortierten Menge ausgehend) der Maximalwert gesucht. Somit müsste zuerst die gesamte Tabelle durchlaufen werden und dann die gefilterte. Außerdem müsste die gefilterte Menge temporär irgendwo abgelegt werden (oder zumindest Zeiger auf die Datensätze, die zu dieser Menge gehören).

MAX-1 halte ich für schneller. MAX-2 läuft nach dem Prinzip, das ich für den allgemeinen MySQL-Fahrplan halte:

• beteiligte Tabellen heranziehen, ggf. verknüpfen.
• mit WHERE vorfiltern
• Gruppieren und Aggregatfunktionen berechnen.
• HAVING berücksichtigen
• Sortieren
• Limitieren
  Nicht vorhandene Klausel müssen natürlich auch nicht berücksitigt werden.

Nach dem Prinzip müsste auch die ORDER/LIMIT-Variante ablaufen. Der Unterschied zu MAX-2 ist, dass die WHERE-gefilterte Menge nicht nur durchlaufen sondern sortiert werden muss. Und dabei stellt sich mir die Frage, die ich an gelernte Informatiker weiterreichen möchte: Was ist schneller, ein Durchlaufen einer Datenmenge oder das Sortieren derselben?

Das Kapitel WHERE Clause Optimization listet einige Optimierungen auf, die MySQL vornimmt. Unter anderem ist zu lesen, dass bei ORDER BY ein Sortieren unterbleiben kann, wenn es bereits einen Index auf der Spalte gibt (wobei so ein Index bei Schreibvorgängen Pflegeaufwand kostet). Welche der drei genannten Varianten weiß diesen Vorteil am besten auszunutzen?

Welche weiteren Optimierungen gibt es für diesen Fall?
(Wo) gibt es einen Dummy-Datenmengengenerator, der eine Testdatenbasis erzeugen kann?

echo "$verabschiedung $name";

[Vinzenz Mai]

Hallo wahsaga,

Ich rede doch von einem Subselect,

hehe, das hatte ich überlesen.

in Kombination mit ORDER BY und LIMIT 1 zur Auswahl des jeweils neuesten Zeitstempels pro Mannschaft

Drei Gründe, warum ich das für keine so gute Idee wie den Einsatz von MAX() halte:

1. Nicht so "intuitiv" wie MAX(), wie bereits Ilja anmerkte.

2. Nicht so portabel wie MAX().
   Zwar endet die Portabilität von SQL-Anweisungen sehr schnell,
   aber warum hier Insellösungen wie LIMIT verwenden, in T-SQL wäre
   es z.B. TOP, wenn es eine Anweisung gibt, die praktisch alle
   DBMS verstehen.

Wie schon gesagt, ich wollte keine Aussage darüber treffen, was da performanter ist - ich glaube allerdings nicht, dass MAX vs. ORDER BY/LIMIT 1 da einen signifikanten Unterschied macht.

3. Nicht so performant wie MAX().
   dedlfix hat es mit Trockenschwimmen vermutet,
   ich habe einfach EXPLAIN auf die Queries losgelassen:

Beide Queries liefern die gleichen Ergebnisse außer bei Extra:
   MAX():          Using where
   ORDER BY/LIMIT: Using where; using filesort

In der von mir verlinkten Doku zu EXPLAIN kannst Du im Abschnitt Extra nachlesen:

<zitat>
   Using filesort
   MySQL must do an extra pass to find out how to retrieve the rows in sorted order.
   [...]
   Using where:
   [...]
   If you want to make your queries as fast as possible, you should look out
   for Extra values of Using filesort and Using temporary.
   </zitat>

Von daher denke ich, dass die Kombination ORDER BY/LIMIT, auch wenn sie das gewünschte Ergebnis liefert, keine gute Idee ist.

Freundliche Grüße

Vinzenz

[wahsaga]

hi,

Beide Queries liefern die gleichen Ergebnisse außer bei Extra:
   MAX():          Using where
   ORDER BY/LIMIT: Using where; using filesort

Ja, kann ich im Test nachvollziehen.

Wie von dedlfix ebenfalls schon vermutet - wenn auf der Spalte ein Index liegt, bekomme ich für die zweite Variante aber das gleiche Ergebnis - Extra: Using where

Von daher denke ich, dass die Kombination ORDER BY/LIMIT, auch wenn sie das gewünschte Ergebnis liefert, keine gute Idee ist.

Ja, ihr habt mich überzeugt - insb. das MAX() intuitiver und portabler ist.

Da möchte ich dann gleich eine Frage anschließen:

Für mein Weblog möchte ich bei der Anzeige eines Beitrages auch die Daten haben, die ich zur Verlinkung des vorherigen und nächsten Eintrages benötige.

Ich habe mir überlegt, wie ich das mit Subselects in einer Query machen kann, und bin zu folgendem Recht simplen Ergebnis gekommen:

  
SELECT *,  
    ( SELECT bkey  
        FROM blog b2  
        WHERE b2.bdate > b1.bdate  
        ORDER BY b2.bdate ASC  
        LIMIT 1  
    ) AS prev,  
    ( SELECT bkey  
        FROM blog b2  
        WHERE b2.bdate < b1.bdate  
        ORDER BY b2.bdate DESC  
        LIMIT 1  
    ) AS next  
FROM blog b1  
WHERE bkey = 'testeintrag'  

(Stört euch zunächst mal bitte nicht am SELECT *, das ist erstmal nur für den Test.)

Sinn des Querys ist, dass ich alle benötigten Daten zum aktuellen Eintrag erhalte, und in den Pseudo-Spalten prev und next den bkey des vorherigen/nächsten (sofern vorhanden, sonst NULL) - und das in einem einzigen Datensatz.
(bkey ist ein Textstring, den ich zur eindeutigen Identifikation eines Eintrages benutze - soll für den Abruf über per mod_rewrite "sprechend" gestaltete URLs dienen, da ich von der Datensatz-ID im URL weg möchte.)

Klappt soweit auch ganz gut - aber beim EXPLAIN der Query bekomme ich auch hier für Extra wieder Using where; Using filesort angezeigt, wenn ich den Index auf der Spalte bdate entferne. Ich hatte vorher einen Index drauf, den ich jetzt zum Testen mal wieder verworfen habe - allerdings denke ich, für eine Spalte, die häufig zum sortieren der Datensätze benötigt wird, ein Index ja durchaus angebracht sein dürfte.

Ich habe jetzt überlegt, ob ich auch hier in den beiden Subqueries ORDER BY/LIMIT 1 irgendwie durch MIN/MAX ersetzen kann - komme aber auf keinen sinnvollen Einsatz dieser Aggregatfunktionen an dieser Stelle.
Gibt es überhaupt einen?

Meine Probleme dabei:

• ich will ja nicht das absolute Maximum/Minimum des Zeitstempels über alle Einträge, sondern jeweils nur aus einer eingeschränkten Menge - aus der aller Datensätze, deren Zeitstempel kleiner bzw. größer als der des aktuellen Eintrages ist.
• ich will ja nicht den Wert aus der Spalte auslesen, deren maximalen/minimalen Wert ich suche (bdate), sondern den dazugehörigen aus einer anderen Spalte (bkey).

(Falls der Aufbau der Tabelle nicht ausreichend klar ist, kann ich diesen gerne nachliefern.
MySQL-Version ist aktuell 4.1.18-nt, bzw. auf meinem Server beim Provider irgendwas > 4.1)

gruß,
wahsaga

[Vinzenz Mai]

Hallo wahsaga,

Für mein Weblog möchte ich bei der Anzeige eines Beitrages auch die Daten haben, die ich zur Verlinkung des vorherigen und nächsten Eintrages benötige.

SELECT *,  
    ( SELECT b4.bkey                 -- Gib mir den Schlüssel  
      FROM blog b4  
      WHERE b4.bdate = (             -- dessen Datumseintrag  
          SELECT MAX(b5.bdate)       -- der größte ist  
          FROM blog b5               -- von allen Einträgen, deren  
          WHERE b5.bdate < b1.bdate  -- Datum kleiner ist als das des  
                                     -- betrachteten Datensatzes  
      )  
    ) AS prev  
    ( SELECT b2.bkey                 -- analog :-)  
      FROM blog b2  
      WHERE b2.bdate = (  
          SELECT MIN(b3.bdate)  
          FROM blog b3  
          WHERE b3.bdate > b1.bdate  
      )  
    ) AS next  
FROM blog b1  
WHERE bkey = 'testeintrag'

setzt wie Dein Statement voraus, dass auf bdate ein UNIQUE-Index liegt, da es bei gleichen Einträgen in dieser Spalte zum Fehler "Subquery liefert mehr als eine Zeile zurück" kommt. Natürlich könnte ich dem mit LIMIT 1 (im äußeren Subquery) entgegenwirken, das Ergebnis wäre ein nicht erreichbarer Eintrag. Vergleichbares sollte bei Deinem Statement übrigens ebenfalls der Fall sein. EXPLAIN kannst Du selber darauf anwenden. *g*

MySQL-Version ist aktuell 4.1.18-nt, bzw. auf meinem Server beim Provider irgendwas > 4.1)

Getestet mit 4.1.16-nt, glücklicherweise hatte ich in meinen Testdaten zwei Einträge mit gleicher Zeit, so dass ich dieses Problem nicht übersehen konnte.

Freundliche Grüße

Vinzenz

[wahsaga]

hi Vinzenz,

danke für deinen Vorschlag.

setzt wie Dein Statement voraus, dass auf bdate ein UNIQUE-Index liegt, da es bei gleichen Einträgen in dieser Spalte zum Fehler "Subquery liefert mehr als eine Zeile zurück" kommt.

Stimmt, hatte ich noch gar nicht explizit bedacht.
Da ich in meinem Blog der einzige Eintrags-Schreiber bin, sind zwei Einträge zum exakt gleichen Zeitpunkt natürlich extrem unwahrscheinlich bis nahezu ausgeschlossen :-)

Aber da ich auf bdate ja eh einen Index zwecks besserer Suchperformance legen wollte, kann ich diesen ja auch eigentlich gleich noch UNIQUE machen.

Natürlich könnte ich dem mit LIMIT 1 (im äußeren Subquery) entgegenwirken, das Ergebnis wäre ein nicht erreichbarer Eintrag. Vergleichbares sollte bei Deinem Statement übrigens ebenfalls der Fall sein.

Dein Vorschlag nutzt jetzt im Subquery allerdings jeweils noch mal einen Subquery - da erscheint mir mein Vorgehen doch irgendwie praktikabler.
Danke trotzdem.

gruß,
wahsaga

[Vinzenz Mai]

Hallo wahsaga,

Da ich in meinem Blog der einzige Eintrags-Schreiber bin, sind zwei Einträge zum exakt gleichen Zeitpunkt natürlich extrem unwahrscheinlich bis nahezu ausgeschlossen :-)

und die Anzahl der Einträge sollte sich in Grenzen halten ;-)

Aber da ich auf bdate ja eh einen Index zwecks besserer Suchperformance legen wollte, kann ich diesen ja auch eigentlich gleich noch UNIQUE machen.

Das solltest Du tun - und Dir dann Dein und mein Statement mit EXPLAIN auswerten lassen. dedlfix' Testdatengenerator wäre mir sehr gelegen gewesen.

Dein Vorschlag nutzt jetzt im Subquery allerdings jeweils noch mal einen Subquery - da erscheint mir mein Vorgehen doch irgendwie praktikabler.

Hast Du schon EXPLAIN benutzt? Ich habe mir vorher _keine_ Gedanken über Performance gemacht, ich war allerdings vom Ergebnis überrascht.

Freundliche Grüße

Vinzenz

[wahsaga]

hi,

und Dir dann Dein und mein Statement mit EXPLAIN auswerten lassen.

Ergebnis EXPLAIN meines Statements mit UNIQUE INDEX auf bdate:

id   select_type      table  type    poss_keys  key     key_len  ref     rows  Extra
1    PRIMARY          b1     range   bkey       bkey    767      NULL    1     Using where
3    DEPENDENT SUBQ.  b2     index   bdate      bdate   8        NULL    75    Using where
2    DEPENDENT SUBQ.  b2     index   bdate      bdate   8        NULL    75    Using where

Und deins unter gleichen Bedingungen:

id   select_type      table  type    poss_keys  key     key_len  ref     rows  Extra
1    PRIMARY          b1     range   bkey       bkey    767      NULL    1     Using where
4    DEPENDENT SUB.   b2     eq_ref  bdate      bdate   8        func    1     Using where
5    DEPENDENT SUB.   b3     index   bdate      bdate   8        NULL    75    Using where; Using index
2    DEPENDENT SUB.   b4     eq_ref  bdate      bdate   8        func    1     Using where
3    DEPENDENT SUB.   b5     index   bdate      bdate   8        NULL    75    Using where; Using index

(Auf bkey liegt übrigens auch noch ein UNIQUE INDEX.)

gruß,
wahsaga

[Ilja]

yo,

kannst du die CREATE und ein paar INSERT beispiele posten, vielleicht fällt mir ja was dazu ein.

Ilja

[wahsaga]

hi,

kannst du die CREATE und ein paar INSERT beispiele posten, vielleicht fällt mir ja was dazu ein.

Danke, aber eigentlich bin ich mit meiner derzeitigen Query ganz zufrieden - und laut EXPLAIN scheint mir die Index-Nutzung auch ausreichend zu sein.

gruß,
wahsaga

[dedlfix]

echo $begrüßung;

dedlfix' Testdatengenerator wäre mir sehr gelegen gewesen.

Vielleicht sollte ich wirklich einen verfassen ...
Dabei könnte ich mir aber nur ein Beispiel an den Trial-Versionen der vorhandenen nehmen.
An meiner offenen Frage sehe ich, dass ihr™ leider auch keinen (gescheiten / von denen) kennt.

echo "$verabschiedung $name";

[wahsaga]

hi,

[...] Statement mit EXPLAIN auswerten lassen.

Das sollte man wirklich viel öfter tun, habe ich gerade wieder mal bemerkt.

Ich habe meine zuvor gepostete Query jetzt mal für zwei Fälle damit untersucht.

Einmal mit in der WHERE-Klausel verglichenem bkey, wenn ich einen bestimmten Eintrag auslesen will - und einmal nur mit ORDER BY bdate DESC LIMIT 1, um den aktuellsten Eintrag zu ermitteln.

Der Faulheit halber hatte ich bisher in meinen Scripten das ORDER BY bdate DESC LIMIT 1 _immer_ in der Query mit drinstehen - und habe dann die WHERE-Bedingung für den bkey dynamisch davor hinzugefügt.
(Obwohl ORDER BY/LIMIT 1 hier natürlich überflüssig ist, weil es zu einem bestimmten bkey nur einen Eintrag geben kann - wie gesagt, einfach der Faulheit halber, weil sich das im Script bequemer zusammenstellen liess.)

EXPLAIN brachte mich dann darauf, dass dies ungünstig ist:

Mit
WHERE bkey = 'xyz' ORDER BY bdate DESC LIMIT 1
kommt nämlich zum Using Where auch noch Using Filesort hinzu.

Wenn ich hingegen nur
WHERE bkey = 'xyz'
benutze, bleibt es beim simplen Using Where.

Ich merke also, seine Queries kann man sich gar nicht zu viel EXPLAINen lassen, und danke euch nochmal für den Hinweis darauf.

gruß,
wahsaga