[MySQL 4.1] order by rand() is traag

[RupS]

Ik draai sinds enige tijd mysql 4.1 en stuit nu op gedrag dat ik niet kan verklaren. Misschien is het een bug, misschien hoort het zo, misschien ligt het aan mij (die kans is het grootst )

Ik heb een tabel, met daarin een aantal VARCHAR velden met indexen. Uit deze tabel wil ik een random record halen. Mijn query is dan:

SQL:

SELECT veld1, veld2, veld3, veld4 FROM tabel ORDER BY RAND() LIMIT 1

Deze query (tabel heeft ~30000 records) duurt ongeveer 7 seconden, wat ik wat lang vind...
Het is ook alleen de RAND() functie die zo traag is.

Wat wel erg snel gaat, is:

SQL:

SELECT id FROM tabel ORDER BY RAND() LIMIT 1

id is een primary key, autoincrement int veld.

Toen dacht ik, ik los het op met een subquery:

SQL:

SELECT veld1, veld2, veld3, veld4 FROM tabel WHERE id=(SELECT id FROM tabel ORDER BY RAND() LIMIT 1)

Dit leidt tot een hangende query en mysql die het niet echt meer wil doen...

In de docs van mysql staat dat je de RAND() functie ook kan seeden met een integer... geprobeerd, geen effect

Heeft er iemand ervaring met het random selecteren van varchar velden in wat grotere tabellen? of iemand die snapt wat er mis gaat, of ik fout doe?

[Verwijderd]

Ik ken MySQL persoonlijk niet zo goed. maar ik kan me voorstellen dat bij deze query eerst alle records een nummer krijgen toegewezen, waarna ze worden gesorteerd, EN die sortering in niet geindexeerd. Dat moet wel veel tijd kosten.

Worden er ook records verwijderd uit je tabel ? oftewel, komen er gaten te vallen in je autoincrement veld ? Zo nee, dan kun je volgens mij beter een random record ID selecteren via code, en die dan ophalen. Als er wel gaten vallen wordt het wat moelijker...

[Victor]

Laat maar, lezen is ook een vak

[ Voor 90% gewijzigd door Victor op 31-12-2004 01:56 ]

[B-Man]

Als ik hier 'SELECT id FROM users ORDER BY RAND() LIMIT 1' uitvoer op een tabel met 880.000 users, waarin id de primary key is (en dus geindexeerd is), is dat ook traag.
'ORDER BY RAND()' is eigenlijk gewoon een bagger oplossing, aangezien hij ieder record in de hele tabel een RAND() waarde moet toekennen.

Zoals FFrenzy al aangaf: als er geen gaten in je 'id' index zitten, dan kun je met een combinatie van max(id) + random waarde tussen 1 en dat maximum veel sneller een random record opvragen. Dit hoeft dan niet eens in code buiten MySQL, daar MySQL inmiddels ook server-side variabelen ondersteunt:

code:

SELECT @random:=ROUND(max(id)*RAND()) FROM table;
SELECT veld1, veld2, veld3, veld4 FROM table WHERE id=@random;

[B-Man]

Laat enzo, maar aangezien ik hier zelf ook al wel eens tegenaan ben gelopen toch even voor je gezocht

code:

SELECT  *  
  FROM users 
  WHERE id >= 
  (
    SELECT ROUND( ( id - 1 ) * RAND(  )  )  + 1 
    FROM users 
    ORDER  BY id DESC
    LIMIT 1
  )  
  ORDER  BY id 
  LIMIT 1

Hiermee kun je zeer snel een random id genereren (zonder alle rijen af te lopen), en door gebruik te maken van '>=' sla je automatisch gaten over als je ze tegenkomt. Deze operator garandeert feitelijk ook dat je altijd een record terug krijgt als je tabel records bevat.

[Janoz]

@B-man: je resultaten zijn echter niet uniform verdeeld. waneer er ergens een gat van 100 reccords valt is de kans dat het eerst volgende reccord gekozen wordt 100x waarschijnlijker dan alle anderen.

[B-Man]

Janoz schreef op vrijdag 31 december 2004 @ 11:35:
@B-man: je resultaten zijn echter niet uniform verdeeld. waneer er ergens een gat van 100 reccords valt is de kans dat het eerst volgende reccord gekozen wordt 100x waarschijnlijker dan alle anderen.

I know. ORDER BY RAND() is echter behoorlijk traag, en dan liever iets minder random maar wel snel.
Die afweging moet de TS zelf maken voor zijn vraagstuk.

[MSalters]

Het hangen is een bug - uiteraard - maar de oplossing van de TS lijkt me toch de meest juiste. Alleen moet je'm dus als twee queries uitvoeren, en eigenlijk transactioneel (iemand kan het record in de tussentijd weggooien). Aan de andere kant, als je toch al wat PHP/Java/C++/... code schrijft, dan kun je dat ook nog wel checken.

[RupS]

Bedankt voor de reacties! Nu Oud, Nieuw is geworden, ben ik weer verder gegaan.
Ik snap nu in ieder geval waarom ORDER BY RAND() zo traag is, maar ik wil toch ook graag dat het wel echt random is.
Dus ik denk dat ik eerst een random id opvraag (SELECT id FROM table ORDER BY RAND() is namelijk wel snel) en dat ik met dat id verder ga.

Niet de meest nette oplossing vind ik, maar beter dan 7 seconden wachten op een query

[Mithrandir]

wat Janoz zegt klopt wel, maar je kunt daar een oplossing kun tvinden: geef een uniek ID (auto_increment) op aan iedere row, zodat je toch geen gaten krijg.t

[RupS]

De kolom is een auto_increment veld, maar er kunnen ook records worden verwijderd, waardoor die gaten nog steeds ontstaan. En die gaten zullen meer dan eens groter zijn dan 100. Vandaar dat ik toch maar voor de tussenoplossing ga.
Ik blijf het alleen vreemd vinden waarom het niet in een subquery kan.

Misschien toch een bugje?