[MYSQL] Abfrageproblem mit GROUP BY und dem letzten Eintrag

[Rain]

Hallo!

Folgendes Problem:

Ich hab ne Logging-Datenbank, die so ziemlich Alles in meinem Webportal mitloggt. Ich möchte eine kleine Box erstellen, welche mir nach Zeit (`Zeitstempel`) sortiert die Benutzer (`Benutzer`) mit zuletzt geloggtem Eintrag (`Was`) anzeigt und zwar innerhalb der letzten 8 Stunden.

Das funktioniert auch alles bis auf den Eintrag (`Was`), da zeigt er mir den scheinbar zuerst gefundenen Eintrag und nicht den zu MAX(`Zeitstempel`) zugehörigen. Ist es möglich das in einer Abfrage unter zu bringen, sonst muss ich halt noch ne zweite Abfrage mit einbauen, extra für das `Was`

SELECT `Was`, `Benutzer`, MAX(`Zeitstempel`) AS `Zeit` 
FROM `logdb` 
WHERE `Zeitstempel` BETWEEN '".(time()-28800)."' AND '".time()."' 
GROUP BY `Benutzer` 
ORDER BY `Zeitstempel` DESC;

Ich bräuchte also sowas wie MAX(`Was`), LAST(`Was`), `Was`.MAX(`Zeitstempel`) ... alles quatsch, aber in die Richtung.

Ich hoffe ihr könnt mir helfen,

Grüße, Rain

 

[Garbanas]

SELECT a.Was, a.Benutzer, a.Zeitstempel AS Zeit
FROM logdb AS a
WHERE a.Zeitstempel >= NOW()-28800
AND NOT EXISTS (
  SELECT * 
  FROM logdb AS b
  WHERE b.Benutzer = a.Benutzer
  AND b.Zeitstempel > a.Zeitstempel
)
ORDER BY a.Zeitstempel DESC;

Zufrieden?

edit: Warum funktioniert deine Abfrage nicht? GROUP BY ist eine Aggregations-Funktion. Du bündelst alle Zeilen, die den gleichen Benutzer haben. Dadurch kannst du MAX() auf Zeitstempel anwenden und erhälst den grössten Wert für Zeitstempel für einen Benutzer. Bei Was weiss MySQL aber nicht, was für einen Wert es nehmen soll von allen möglichen Werten und nimmt einfach den ersten. Bei anderen Datenbanken gäbe das sogar den NULL-Wert oder die Abfrage meckert, dass das nicht genau definiert sei. MySQL ist da fehlertolerater (kann aber auch recht nervig sein beim Entwickeln).

 

[Rain]

Zufrieden?

Fast!

a.Zeitstempel >= NOW()-28800 zeigte mir nichts mehr an

erst a.Zeitstempel <= NOW()-28800 brachte mir wieder Ergebnisse.

....

ARGH!

Also lokal hat bei mir Alles gut funktioniert mit deiner Version, aber da hatte die Datenbank auch nur 607 Einträge und 2 User,

Online mit über 6000 Einträgen und 50 User brach grad mein (xampp-)mysql-server zusammen mit extremer Prozessorauslastung

Kanns sein dass an der Abfrage irgendwas sehr performancelastiges ist?

 

[ice-breaker]

Hast du denn Indexe definiert?

 

[XunnD]

Eigentlich sollte auch ohne Index gut laufen - 6000 Einträge sind nicht wirklich eine Herausforderung für MySQL.

Einfach mal ein EXPLAIN SELECT ausführen.

 

[Rain]

ja, die datensätze sind anhand einer id indexiert.

was sagt mir das EXPLAIN SELECT dann?

Hab die Onlinedatenbank mal nach lokal kopiert und die Abfrage ausgeführt --> der Server rennt sich einfach nur tot

 

[ice-breaker]

Eigentlich sollte auch ohne Index gut laufen - 6000 Einträge sind nicht wirklich eine Herausforderung für MySQL.

theoretisch ja, praktisch wird ein FullTable-Scan nötig, also alle Datensätze von der Festplatte laden, was durchaus einiges an IO-Last produziert. Wenn MySQL nicht korrekt konfiguriert ist (wovon ich nun ausege, da Rain da nicht sehr viel Erfahrung zu haben scheint, wird das nochmal eine Ecke schlimmer)
Da Garbanas Query zudem ein Dependent Subquery ist, ist der Query ein Performance-Killer. 6000 Datensätze von der Platte laden, und nochmal für jeden, auf den die Selektion passt einen weiteren Subquery der wieder einen FullTable-Scan verursacht... Da sind wir bei quadratischen Aufwand: O(n^2)
Sollte die Selektion des äußeren Querys nur auf 100 Werte passen, dann sind das schon 6000 + 100 * 6000 gelesene Datensätze von der Hdd.

ja, die datensätze sind anhand einer id indexiert.

das sie nach einer id indexiert sind, bringt dir aber nichts, wenn du nach dem Zeitstempel suchst, also einen Index auf den Zeitstempel legen.

Zusätzlich habe ich noch einen perfekt optimierten Query für dich:

SELECT Was, Benutzer, MAX(Zeitstempel) AS Zeit 
FROM logdb
WHERE (Benutzer, Zeitstempel) IN(
  SELECT Benutzer, MAX(Zeitstempel)
  FROM logdb
  WHERE Zeitstempel BETWEEN '".(time()-28800)."' AND '".time()."' 
  GROUP BY Benutzer
);

Edit: MySQL optimiert den Query wieder kaputt, ich habe es fast geahnt. Am effizientesten ist es dann, wenn du den Query manuell zusammenbaust:

$query = 'SELECT Benutzer, MAX(Zeitstempel)
  FROM logdb
  WHERE Zeitstempel BETWEEN '".(time()-28800)."' AND '".time()."' 
  GROUP BY Benutzer
';
$results = ....

$where = // jeden Datensatz verknüpfen durch (Benutzer = 50 AND Zeitstempel = 7) OR (Benutzer = 51 AND Zeitstempel = 9) ....

$query = 'SELECT Was, Benutzer, MAX(Zeitstempel) AS Zeit FROM logdb WHERE ' . $where;

Wenn du Pech hast, entscheidet sich aber auch da MySQL, dass ein FulltableScan effizienter ist. Partitionierung anhand des Zeitstempels könnte da helfen oder ggf. in einer Shadow-Tabelle die Werte vorberechnet speichern, je nach Anwendungszweck und Anzahl der Ausführungen das effizienteste.

 

[Rain]

Joa, ich wollte grade schon schreiben .. perfekter query? ich hab nach der ausführung erst mal nen system-freeze gehabt.

das was du mir vorschlägst ist quasi das, was ich versuchen wollte zu umgehen bzw in eine abfrage zu packen, nämlich nach der abfrage des maximalen zeitstempels ein weiteres query zu schicken wo ich nochmal die zugehörige `Was` Spalte abzurufen.

Irgendwie schade dass es da keine wirklich einfache und ressourcenschonende Lösung gibt. Die 2. Abfrage ist natürlich kein Beinbruch.

Was deine Behauptung angeht, ich habe von mysql nicht viel Ahnung ... du hast recht! Einfach Abfragen, Inserts und Updates sind wohl nur die Basics, aber mit >200 Datenbanken in meinen System kam ich bis jetzt eigentlich bestens klar. Du hast auch recht, dass mein MySQL - Server nicht wirklich konfiguriert ist, bis auf die Standard-Installationseinstellungen. Bis jetzt ist mir aber nichts Problematisches aufgefallen. Kannst du mir vielleicht sagen, was man an einem MySQL Server eingestellt haben sollte, was mich vor größeren Problem bewahren kann?

 

[ice-breaker]

Joa, ich wollte grade schon schreiben .. perfekter query? ich hab nach der ausführung erst mal nen system-freeze gehabt.

hast du auch den geforderten Index angelegt?
Wie gesagt hat MySQL die Hälfte des Querys wieder kaputt optimiert, der innere Query funktioniert wunderbar, für den äußeren entscheidet sich MySQL bei mir aber einen FullTable-Scan zu machen, dies scheint an einer fehlenden Optimierung von multiplen Spalten für das IN()-Statement zu liegen.

das was du mir vorschlägst ist quasi das, was ich versuchen wollte zu umgehen bzw in eine abfrage zu packen, nämlich nach der abfrage des maximalen zeitstempels ein weiteres query zu schicken wo ich nochmal die zugehörige `Was` Spalte abzurufen.

mir ist klar, dass du dies umgehen wolltest, aber wenn es nicht anders geht.
Probier mal meinen inneren Query, funktioniert der bei dir richtig? Denn bei mir ist MySQL intelligent genug den Benutzer zu wählen, der zum maximalen Zeitstempel gehört (kein Sort!), dies kann aber durch eine andere MySQL-Version gleich wieder anders sein. Das Verhalten dieser Operation ist auch im SQL-Standard nicht erwähnt, eigentlich dürfte es nicht möglich sein, MySQL hat da aber eine Art Erkennung eingebaut, um zu vermuten, was du meinst.

Irgendwie schade dass es da keine wirklich einfache und ressourcenschonende Lösung gibt. Die 2. Abfrage ist natürlich kein Beinbruch.

Das Problem ist einfach, dass für dich die Abfrage einfach klingt, in echt aber etwas komplett anderes dahinstersteckt:
Finde für jeden Benutzer in dem Interval für den Zeitstempel den maximalen Zeitstempel und gib mir den Datensatz aus.
Problem ist eben, dass du Grouping-Functions und den Rest nicht wirklich sinnvoll mischen kannst.

Bis jetzt ist mir aber nichts Problematisches aufgefallen. Kannst du mir vielleicht sagen, was man an einem MySQL Server eingestellt haben sollte, was mich vor größeren Problem bewahren kann?

Es gibt dazu genug Informationen im Netz
Einerseits um die MySQL-Config "my.cnf" einzustellen, andererseits auch Query-Optimierung bei der dann meist nur die Basis mit Indexen erklärt werden (für dich ausreichend)