Select random record from joined table

[ErikBooy007]

Goedemiddag dames, heren,

Ik heb een (volgens mij) vrij simpele vraag, maar kan het antwoord er niet echt op vinden.

Ik heb twee tabellen (hieronder zeer gesimplificeerd weergegeven):

ALBUMS
[table="width: 500, class: dotted"][tr][td]AID[/td][td]TITLE[/td][td]EVENTDATE[/td][/tr]
[tr][td]1[/td][td]Album Titel[/td][td]2013-07-12[/td][/tr]
[tr][td]2[/td][td]Album Titel 2[/td][td]2013-07-15[/td][/tr]
[/table]

PHOTOS
[table="width: 500, class: dotted"][tr][td]PID[/td][td]AID[/td][td]IMAGENAME[/td][/tr]
[tr][td]1[/td][td]1[/td][td]img_001.jpg[/td][/tr]
[tr][td]2[/td][td]1[/td][td]img_002.jpg[/td][/tr]
[tr][td]3[/td][td]1[/td][td]img_003.jpg[/td][/tr]
[tr][td]4[/td][td]1[/td][td]img_004.jpg[/td][/tr]
[tr][td]5[/td][td]1[/td][td]img_005.jpg[/td][/tr]
[tr][td]6[/td][td]2[/td][td]img_001.jpg[/td][/tr]
[tr][td]7[/td][td]2[/td][td]img_002.jpg[/td][/tr]
[tr][td]8[/td][td]2[/td][td]img_003.jpg[/td][/tr]
[tr][td]9[/td][td]2[/td][td]img_004.jpg[/td][/tr]
[tr][td]10[/td][td]2[/td][td]img_005.jpg[/td][/tr]
[/table]

Nu wil ik alle albums selecteren, plus van elk album een willekeurige foto. Nu krijg ik altijd het eerste resultaat. De query die ik gebruik is als volgt:

[SQL]
SELECT
ALBUMS.AID,
ALBUMS.TITLE,
ALBUMS.EVENTDATE,
PHOTOS.PID,
PHOTOS.IMAGENAME
FROM
ALBUMS
JOIN
PHOTOS ON ALBUMS.AID = PHOTOS.AID
GROUP BY
PHOTOS.AID
ORDER BY
ALBUMS.EVENTDATE DESC,
ALBUMS.TITLE ASC
[/SQL]

UPDATE:

Heb nu de volgende query:

[SQL]
SELECT
ALBUMS.AID,
ALBUMS.TITLE,
ALBUMS.EVENTDATE,
PHOTOS.IMAGENAME
FROM
ALBUMS
JOIN
(
SELECT
AID,
PID,
IMAGENAME
FROM
PHOTOS
ORDER BY
RAND()
) AS PHOTOS ON ALBUMS.AID = PHOTOS.AID
GROUP BY
PHOTOS.AID
ORDER BY
ALBUMS.EVENTDATE DESC,
ALBUMS.TITLE ASC
[/SQL]

Maar we weten allemaal dat ORDER BY RAND() niet in een snelle query thuishoort toch? ;-) Deze query duurt nu al gemiddeld 30ms, op tabellen met 19 albums en 5078 foto's. Dat zullen er nog véél meer worden.

 

[Tha Devil]

Twee opties die ik zo gauw kan bedenken:

1: Subquery
[sql]
SELECT
A.*,
(SELECT
IMAGENAME
FROM
PHOTOS P
WHERE
P.AID = A.AID
ORDER BY RAND()
LIMIT 1
)
FROM
ALBUMS A
ORDER BY
A.EVENTDATE DESC,
A.TITLE ASC;
[/sql]
http://sqlfiddle.com/#!2/39d65/1 (kleine aanpassing aan je imagenames gedaan zodat ik kon zien of ie uit album 2 kwam)

2: Pad naar een random afbeelding bij de album-tabel toevoegen.

[edit]Nog een derde optie:
Eerst een query uitvoeren met de albums en daarna een aparte query die een random afbeelding opvraagt.

Het is even de vraag welke optie performance-wise de beste is.
[/edit]

 

[ErikBooy007]

Thanks! Dit werkt perfect en de query runt ook nog iets sneller. Hij duurt nu 24ms om precies te zijn Top!

 

[PgVincent]

Maar we weten allemaal dat ORDER BY RAND() niet in een snelle query thuishoort toch? ;-)

We weten helaas ook dat je er nit aan gaat ontkomen

Je zou het moeten benchmarken maar ik vermoed dat er niet veel verschil gaat zitten tussen eerst alle foto's random sorteren en dan per album 1 foto kiezen, of alle albums ophalen en per album de foto's van dat album random sorteren en er één pakken. Ik ben bang dat de tweede methode bij een groter aantal albums trager gaat zijn omdat je botweg veel(!) meer queries doet.

Maar dat kun je natuurlijk gewoon testen door een paar honderdduizend albums aan te maken met een paar miljoen foto's en te zien wat er gebeurt.
En om het helemaal leuk te maken; de performance van een methoed is sterk afhankelijk van de afmeting van de database (en de resources van de server) dus wat het kan goed zijn dat je huidige methode het snelst is tot je een bepaald aantal albums+foto's bereikt, waarna een andere aanpak weer sneller gaat zijn (die met een lager aantal albums dus trager is).

In een database met een beetje ballen kun je hier vast wel iets leuks doen met windowing, dat is specifiek bedoeld om data over een groepje records op te halen zonder te groeperen.