MySQL: m:n Beziehung mit Sortierung und Limit (-> Historie)

[Peter Körner]

Hallo

Ich bin schon öfter über folgenden Fall gestolpert und eine soso-lala-Lösung habe ich auch, aber ich kann kaum glauben, dass es keine bessere gibt.

Gegeben sei folgende Datenbankstruktur:

Eine user-Tabelle, welche eine Liste mit Benutzerinformationen enthält:

  
CREATE TABLE `user` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `vorname` varchar(80) NOT NULL,  
  `nachname` varchar(80) NOT NULL,  
  PRIMARY KEY  (`id`)  
)  

Eine status-Tabelle, welche verschiedene Stadien enthält, die ein Benutzer haben kann:

  
CREATE TABLE `status` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `title` varchar(80) NOT NULL,  
  PRIMARY KEY  (`id`)  
)  

Eine userstatus-Tabelle, welche einem User eine beliebige Anzahl stadien zuweist, die er in seinem Werdegang durchlaufen hat (Historie). Zu jeder Zuweisung ist ein Timestamp gespeichert, sodass es möglich ist den aktuellen Wert anhand dieser Sortierung zu ermitteln.

  
CREATE TABLE `userstatus` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `userid` int(10) unsigned NOT NULL,  
  `statusid` int(10) unsigned NOT NULL,  
  `tstamp` timestamp NOT NULL default CURRENT_TIMESTAMP,  
  PRIMARY KEY  (`id`),  
  KEY `statusid` (`statusid`),  
  KEY `userid` (`userid`),  
  KEY `fullrow` (`tstamp`,`userid`,`statusid`)  
)  

Falls es sich um eine INNO-Datenbank handelt, können auch noch die zugehörigen Konstraints hinzugefügt werden:

  
ALTER TABLE `userstatus`  
  ADD CONSTRAINT `userstatus_ibfk_4` FOREIGN KEY (`userid`) REFERENCES `user` (`id`),  
  ADD CONSTRAINT `userstatus_ibfk_5` FOREIGN KEY (`statusid`) REFERENCES `status` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION;  

Die Aufgabe ist es nun ein Query zu finden, welches alle Benutzer mit ihrem aktuellen Status auflistet.

1. Versuch:

  
SELECT user.id, user.vorname, user.nachname, status.title as status  
FROM user  
  
JOIN userstatus  
  ON user.id = userstatus.userid  
JOIN status  
  ON status.id = userstatus.statusid  
  
GROUP BY user.id  
ORDER BY userstatus.tstamp  

Aufgrund der Reihenfolge in der SQL zunächst GROUP und anschließend SORT abarbeitet, liefert das folgende Query NICHT den aktuellsten Status sondern den, der beim Gruppieren ausgewählt wurde und welcher das ist ist nicht eindeutig definierbar.

Stattdessen muss an dieser stelle ein manuelles Subselect verwendet werden:

  
SELECT user.id, user.vorname, user.nachname, status.title as status  
FROM user  
  
JOIN (SELECT statusid, userid, tstamp FROM userstatus ORDER BY tstamp DESC) AS userstatus  
  ON user.id = userstatus.userid  
JOIN status  
  ON status.id = userstatus.statusid  
  
GROUP BY user.id  

Soweit so gut. Wir haben Performance-Tests mit Datenmengen gemacht, wie sie für unsere Anwendung typisch wären: 5'000 Benutzer, 15'000 Statuszuweisungen und 10 Stadien. Damit ergibt sich auf einem CentOS-Sytem mit 2x Intel Pentium D CPU 3.00GHz, 1GB Ram und einem MySQL-Server Version 5.0.48 eine Laufzeit von 0.1027 sek. Das ist eigentlich sehr gut.

Nun gibt es aber auch Benutzer, die noch nie einen Status zugewiesen bekommen haben, weswegen die JOINs zu LEFT JOINs geändert werden müssen:

  
SELECT user.id, user.vorname, user.nachname, status.title as status  
FROM user  
  
LEFT JOIN (SELECT statusid, userid, tstamp FROM userstatus ORDER BY tstamp DESC) AS userstatus  
  ON user.id = userstatus.userid  
JOIN status  
  ON status.id = userstatus.statusid  
  
GROUP BY user.id  

Dadurch steigt die Laufzeit schlagartig auf 9.5034 sek. an.

Unsere Lösung war, das ganze mit zwei Queries zu erledigen: zunächst das vorletzte, welches alle User, die einen Status haben, zurückgibt und anschließend noch das folgende Query, welches alle anderen zurückgibt:

  
SELECT * FROM `user` WHERE id NOT IN (SELECT DISTINCT userid FROM userstatus);  

Beide zusammen laufen nur ungefähr 0.1 sek. was ungefähr 1/100 des LEFT JOIN-Queries entspricht. Nur frage ich mich, ob es nicht eine schönere Lösung für dieses Problem gibt. Kompliziert wird es auch, wenn eine (oder mehrere) weitere Historien-Tabelle in den JOIN mit aufgenommen werden sollen (beispielsweise noch eine abteilungs-Historie oder eine Standort-Historie..).

Wenn einer von euch eine Idee hat, wie man dieses Problem besser angehen kann, wäre ich um einen krzen Post oder eine E-Mail sehr Dankbar. Ich kann auch gerne Demodaten zur verfügung stellen, auch wenn ihr euch mit http://www.generatedata.com/ selbst sehr einfach selbst welche erzeugen könnt.

Liebe Grüße,
Peter

[globe]

n'abend,

Wenn einer von euch eine Idee hat, wie man dieses Problem besser angehen kann, wäre ich um einen krzen Post oder eine E-Mail sehr Dankbar. Ich kann auch gerne Demodaten zur verfügung stellen, auch wenn ihr euch mit http://www.generatedata.com/ selbst sehr einfach selbst welche erzeugen könnt.

Wenn ich explizit eine Historie haben möchte, lege ich dazu meistens eine eigene (separate) Tabelle an. Ich mache das, weil die "schalen Daten", die ich für mein »Daily Business« eigentlich nicht brauche nicht betrachten muss.

In deinem Fall hat ein Benutzer zu einem Zeitpunkt immer nur einen Status. Warum diesen also nicht als Attribut des Benutzers angeben? Bei Änderungen würdest du dann die alten Daten in die Historie schreiben.

Um aus einer Menge von ("versionierten") Datensätzen die aktuellen herauszufinden, bietet sich eine korrelierende Subquery an:

SELECT s.*  
FROM userstatus s  
WHERE s.id = (  
  SELECT MAX(i.id)  
  FROM userstatus i  
  WHERE i.userid = s.userid  
);

(ID-Attribut genommen, weil fortlaufender PrimaryKey. tstamp wäre auch möglich)

weiterhin schönen abend...