jeudi 8 février 2018

Mysql RAND() function limitation by multiplication

i get into an interesting issue with MySQL. When i try to multiply RAND() function with some big integer, i get max random number really small. Here is my MySQL query, that should be a very fast random query, but it returns ID max 36000, even when there are 4600000+ IDs.

SET @maxID=(SELECT MAX(id) FROM property); #it's about 4600000

SELECT * FROM property
WHERE 
downloaded_at IS NULL
AND id >= FLOOR(1 + RAND() * @maxID) #this returns max +/-36000
LIMIT 100

When i move this code into plain SELECT query, everything is fine

SELECT FLOOR(1 + RAND() * (SELECT MAX(id) FROM property))

Could someone please explain, why this error occurs? Thank you!


edit: Hm, somehow when i remove downloaded_at IS NULL it comes to sences, ID's are higher, but results are not that random anymore.




Aucun commentaire:

Enregistrer un commentaire