[PHP] alternative to ORDER BY RAND() on large tables

[SplitIce]

Never run ORDER BY statements on large tables (e.g 4M tables).

Heres some code that will work as an alternative in most cases.

$resv = mysql_query('SELECT COUNT(*) FROM v3links');
$row = mysql_fetch_row($resv);
$sval = rand(0,$row[0]-15001);
$query=mysql_query("SELECT `id`,`fileid`,`type`, `filename` FROM `v3links` LIMIT ".$sval.",15000"); 

enjoy.

SplitIce Reviewed by SplitIce on 27th Aug 2009. [PHP] alternative to ORDER BY RAND() on large tables Never run ORDER BY statements on large tables (e.g 4M tables). Heres some code that will work as an alternative in most cases. $resv = mysql_query('SELECT COUNT(*) FROM v3links'); $row = mysql_fetch_row($resv); $sval = rand(0,$row-15001); $query=mysql_query("SELECT `id`,`fileid`,`type`, `filename` FROM `v3links` LIMIT ".$sval.",15000"); Rating: 5

[DEViANCE]

Im guessing this is faster than say:

SELECT * FROM my_table ORDER BY RAND() LIMIT 1;

??

[immortalxx]

rand is very slow, so the more rows u add the slower it gets. rand on php is much faster

[hip_hop_x]

$sval = rand(0,$row[0]-15001);
15001 isn't correct, what if you don't have 15001 counts ?

[SplitIce]

Lol i said large table i.e 5m rows like me. I got a new version at home