MySQL: Sortierung nach aktuellster ID bei GROUP BY

[Pyramide]

Ich kann zwar mangels nachprüfbarkeit nicht sagen wie das von der Performance ausieht, aber

Code: Alles auswählen

SELECT ... FROM ..., (SELECT * FROM phpbb_att_links ORDER BY attach_id DESC) AS al

sollte funktionieren.

[S2B]

@mgutt: Warum baust du das in die Unterabfrage ein?

Code: Alles auswählen

WHERE al.attach_id = al2.attach_id

[easygo]

Das wird bei 50.000 Attachments etwas schwierig

Jo das klingt nach viel, aber andererseits..

Wenns im Grunde "nur" ums Herausfiltern der max ids geht und wenn sich die Attachments halbwegs
gut verteilen, wär schon auch ein LIMIT vertretbar.. oder seh ich das falsch? ^^

SELECT * FROM phpbb_att_desc ORDER BY attach_id DESC LIMIT 0,999 (als Beispiel)

[mgutt]

@ pyramide:

Vielen Dank! Das war die Lösung: (Abfragezeit ca. 0,4 - 0,5 Sekunden)

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) count, ad.attach_id, ad.physical_filename, ad.real_filename, ad.extension, ad.filetime, ad.thumbnail 
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, (SELECT attach_id, post_id FROM phpbb_att_links ORDER BY attach_id DESC) al, phpbb_att_desc ad
WHERE t.topic_id = p.topic_id
AND p.post_id = al.post_id
AND al.attach_id = ad.attach_id
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

@ S2B
Ich hatte es auch ohne probiert, aber da nimmt er einfach die höchste ID von allen Attachments. Also fehlte der Bezug zur eigentlichen forum_id und den wollte ich auf diese Art herstellen (was ja so auch nicht ging ). Hier die Abfrage ohne WHERE-Part, aber ohne Erfolg:

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) AS count, ad.*
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, phpbb_att_links al, phpbb_att_desc ad
WHERE t.topic_id = p.topic_id
AND p.post_id = al.post_id
AND ad.attach_id =
(

SELECT MAX(al2.attach_id)
FROM phpbb_att_links al2

)
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

@ easygo
Danke! Das wäre natürlich denkbar. Wobei dann tatsächlich die Frage ist, ob die Anzahl an Alben und wann jemand das letzte Bild hochgeladen hat dann wirklich exakt abgedeckt werden. Ich könnte mir tatsächlich vorstellen, dass 1000 Bilder nicht ausreichen könnten, da nicht alle Attachments dem Album zugewiesen sind und wenn 1000 Bilder außerhalb von Alben hochgeladen würden, dann müsste man die Zahl entsprechend z.B. auf 2000 anheben, was aber immernoch kein großes Problem darstellen dürfte, da es ja "nur" IDs sind. Aber Pyramides Version funktioniert ja jetzt

@ Herausforderung
Die aktuellsten drei Bilder kann man aber nicht auf diese Art ermitteln oder? Ich habe das jetzt nicht großartig zu Ende gedacht, denke aber, dass es so schon daran hapern wird, dass man ja das vorvorletzte und vorletzte Bild recht schwer herausfiltern kann.

@ Pyramide & Easygo
Ihr beiden seit gar nicht so weit auseinander gewesen, wir mir jetzt aufgefallen ist.

Setze ich nun Limits, so wird die Abfrage von Pyramide viel schneller:

Code: Alles auswählen

SELECT f.forum_id, f.forum_name, COUNT(al.attach_id) count, ad.attach_id, ad.physical_filename, ad.real_filename, ad.extension, ad.filetime, ad.thumbnail
FROM phpbb_topics t, phpbb_forums f, phpbb_posts p, (SELECT attach_id, post_id FROM phpbb_att_links ORDER BY attach_id DESC LIMIT 3000) al, phpbb_att_desc ad
WHERE t.topic_id = p.topic_id
AND p.post_id = al.post_id
AND al.attach_id = ad.attach_id
AND t.forum_id = f.forum_id
AND t.topic_sub_type = 12
AND t.forum_id IN(1, 2)
GROUP BY f.forum_id
ORDER BY f.forum_name ASC

Bei Limit 1000 und 2000 fehlten ein paar Alben. Bei Limit 2500 sind sie vollständig aufgelistet. Daher habe ich jetzt mal als Limit 3000 eingestellt.

Die Abfrage ist nun 0,04-0,05 Sekunden schnell. (also um den Faktor 10 schneller geworden)

Jetzt ist die Frage natürlich offen, wie man ein korrektes Limit auf den Wert genau ermitteln müsste, um die Abfrage dynamischer zu machen

[Pyramide]

Jetzt ist die Frage natürlich offen, wie man ein korrektes Limit auf den Wert genau ermitteln müsste, um die Abfrage dynamischer zu machen

Das ginge wohl nur durch Trial&Error.

Überhaupt wäre die bessere Lösung, erst die Foren und dann die Attachments in einer eigenen (Unter-)Abfrage zu ermitteln. Denn die Verwendung von GROUP BY ohne Aggregatfunktionen funktioniert sowieso nicht mit allen DBMS. MySQL nimmt dann zwar automatisch den "ersten" Datensatz, aber daß das auch nicht ohne Probleme abläuft, sieht man ja hier.

[mgutt]

Naja, wenn man ehrlich ist, wäre die schnellste und beste Lösung eh eine oder mehrere zusätzliche Spalten in der Forumtabelle mit der/den letzten Attach_id(s).

Mal sehen, ob ich das beim Upload nicht einfach mal mache. Resultiert ja unter anderem eine höhere Abfragegeschwindigkeit.

EDIT:
Also das mit dem Limit war doch zu voreilig. Dann stimmt nämlich das COUNT() nicht mehr.

Also dann ohne Limit und eben 0,5 Sekunden.