GROUP BY "umkehren"?

T

TerraTux

Well-known member
ID: 141382
L
20 Mai 2006
427
50
Hallo,

ich stehe grad vor einem kleinen Problem, bei dem ich entweder keine Lösung sehe, oder eben diese vor lauter Bäume nicht mehr.

Es geht dabei um die Anzeige der letzten 6 hochgeladenen Bilder von unterschiedlichen Benutzern zu unterschiedlichen Zeiten. Dazu habe ich eine Tabelle mit den Feldern:

- media_id (auto_increment)
- album_id (weil die Bilder in Alben einsortiert werden können)
- user_id (wie es der Name schon sagt)
- media_time (Upload-Zeitstempel)

Bei dem Ergebnis sollen die User nur einmal angezeigt werden. Wenn also user Y gestern ein Bild hochgeladen hat, und User X heut 10 neue Bilder, dann soll X einmal an erster stelle stehen, und Y einmal an zweiter Stelle und so weiter.

Mein bisheriger versuch, die Rows nach user_id zu Gruppieren und nach media_time zu sortieren schlägt fehl, da bei der Gruppierung das erste Vorkommen von Relevanz ist.
D.h. wenn nach mir 6 Andere ein neues Bild hochladen, rutsch ich wegen der Limitierung hinten raus. Lad ich danach eins hoch, steh ich trotzdem nicht mit drin weil der erste gefundene Eintrag mit meiner UserID einen Zeitstempel von Anno weißdergeierwann hat.

Die Query dazu sieht momentan wiefolgt aus:
PHP: 
SELECT a.media_file, u.user_id, u.user_name FROM user_album_files a
   LEFT JOIN user_album_folders f ON (a.album_id = f.id)
   LEFT JOIN user_table u ON (a.user_id = u.user_id)
   WHERE f.album_viewtype = 0
      AND a.is_profile = 0
      AND a.is_image = 1
   GROUP BY a.user_id
   ORDER BY a.media_time DESC LIMIT 6

Wenn Ihr irgendeine Idee habt wie ich das Lösen kann, ohne dabei X zusätzliche Queries haben zu müssen, immer her damit :)
Die Statusbits (is_profile, etc) sowie die Verknüpfungen zwischen den Tabellen können in Beispielen vernachlässigt werden. Wichtig ist mir nur die passende Query der letzten 6 Bilder von 6 unterschiedlichen Benutzern.

Vielen Dank & Gruß,
TT
 
versuch es mit:
ORDER BY max(a.media_time) DESC LIMIT 6

weiß nciht wie die Performance ist aber vielleciht auch sowas:

Code: 
SELECT a.media_file, u.user_id, u.user_name FROM user_album_files a 
   LEFT JOIN user_album_folders f ON (a.album_id = f.id) 
   LEFT JOIN user_table u ON (a.user_id = u.user_id) 
   WHERE f.album_viewtype = 0 
      AND a.is_profile = 0 
      AND a.is_image = 1 
  and a.mediaid in (select max(mediaid) from user_album_files group by  album_id)

Unter umständen ist es da sogar deutlich schneller, wenn du die Unterabfrage erst dir holst und das Ergebnis mit ind die Abfrage einbaust (weiß nicht wie schlau Mysql ist, bei MSSQL hatte ich den fall shcon, das es ratsam ist).
 
Hallo,

Danke, aber:

Bububoomt schrieb:
versuch es mit:
ORDER BY max(a.media_time) DESC LIMIT 6
Zum Vergrößern anklicken....
Damit bekomme ich nur genau einen Datensatz aus der DB. :(

Bububoomt schrieb:
weiß nciht wie die Performance ist aber vielleciht auch sowas:

Code: 
SELECT a.media_file, u.user_id, u.user_name FROM user_album_files a 
   LEFT JOIN user_album_folders f ON (a.album_id = f.id) 
   LEFT JOIN user_table u ON (a.user_id = u.user_id) 
   WHERE f.album_viewtype = 0 
      AND a.is_profile = 0 
      AND a.is_image = 1 
  and a.mediaid in (select max(mediaid) from user_album_files group by  album_id)

Unter umständen ist es da sogar deutlich schneller, wenn du die Unterabfrage erst dir holst und das Ergebnis mit ind die Abfrage einbaust (weiß nicht wie schlau Mysql ist, bei MSSQL hatte ich den fall shcon, das es ratsam ist).
Zum Vergrößern anklicken....
Wie es Ice schon angedeutet hat, geht damit die Performance akut in den Keller. Ist für die Indexseite nicht wirklich angebracht.

Aber MAX() liefert doch "nur" den größten Wert. In dem Falle MAX(a.media_id) wäre das die letzte Autoinkrementelle ID.

Ich werd das Ding dann wohl erstmal mit einer while-Schleife machen müssen, in der ich ein Array mit den zutreffenden user_ids aufbaue...

[NACHTRAG]
Okay, hab nun das LIMIT und die Gruppierung weggelassen. In der Whileschleife dann ein temporäres Array mit den UserIDs gefüllt, und dieses Array auf Vorkommen geprüft, bzw. die Schleife bei einer Arraygröße von 6 abgebochen.

Seitenzeit: 0.182s
Queries: 37
Memory Usage: 8.10 MB
(bei deaktiviertem Cache)

Rennt für eine Portalseite IMHO noch im brauchbaren Bereich.


Wenn trotzdem noch jemand etwas performanteres weiß... :)

Gruß
 
Zuletzt bearbeitet:
TerraTux schrieb:
Rennt für eine Portalseite IMHO noch im brauchbaren Bereich.

Wenn trotzdem noch jemand etwas performanteres weiß... :)
Zum Vergrößern anklicken....
Redundanz? Zusätzliche Tabelle mit User-ID als Primärschlüssel, Bild-ID und Datum des letzten Bilds als Daten dran.
Code: 
[FONT=Courier New][B][COLOR=#9932cc]SELECT[/COLOR][/B] user_id, ...
[B][COLOR=#9932cc]FROM[/COLOR][/B] latest_user_pics
[B][COLOR=#9932cc]JOIN[/COLOR][/B] ... [B][COLOR=#9932cc]ON[/COLOR][/B] ... = [/FONT][FONT=Courier New]latest_user_pics.media[/FONT][FONT=Courier New]_id
[B][COLOR=#9932cc]JOIN[/COLOR][/B] ...
[B][COLOR=#9932cc]JOIN[/COLOR][/B] ...
[B][COLOR=#9932cc]ORDER[/COLOR][/B] [B][COLOR=#9932cc]BY[/COLOR][/B] media_time [B][COLOR=#9932cc]DESC[/COLOR][/B];
[B][COLOR=#9932cc]LIMIT[/COLOR][/B] 6;
[/FONT]
Du müsstest halt beim Hochladen und Löschen von Bildern diese Tabelle zusätzlich auf den aktuellen Stand bringen. Dafür muss die Index-Seite sich nicht den Kopf drüber zerbrechen, welche Pics die richtigen sind.
 
Zuletzt bearbeitet:
so nun habe ich nen Moment mehr Zeit als heute morgen:
Wie wäre es ein Attribut "last_image" in der Tabelle "user_tabelle" einzuführen, welches den Zeitstempel des MAX(user_album_files.media_time) representiert?
Dies wäre eine gezielte Denormalisierung und du könntest die Media_Time per "Hand" pflegen (in das Script an den richtigen Stellen Funktionen einbauen, die den Zeitstempel updaten), oder du könntest es mittels Triggern auf der Tabelle user_album_files.media_time realisieren. Wobei die erste Methode die beste Performance bietet.

So etwas sehr ähnliches habe ich schon desöfteren geraten und bei einigen Projekten führte es zu einer beachtlichen Effiziensteierung, denn dort wurde es bisher mit Subselect oder teuren Groups realisiert.
 
Du musst dich einloggen oder registrieren, um hier zu antworten.
Zurück
Oben