* Thus wrote Burrito Warrior ([EMAIL PROTECTED]): > > This would: > /* selecting a random record, where n = 1. Works fine on small tables */ > mysql> SELECT value FROM table ORDER BY RAND() LIMIT n; > > /* selecting a random record. This is good on large tables if numbering > sequence is used */ > mysql> SET @val = FLOOR(RAND() * n) + 1; > mysql> SELECT value FROM table WHERE ID = @val;
If two queries is acceptable: select a count() on a unique/primary key, it should minimal overhead since the value is obtained very quickly with those columns. then do a limit... SELECT FLOOR(RAND() * COUNT(id)) + 1 as n FROM table; SELECT value FROM table LIMIT n, 1 return 1 record starting to the nth record. There still lies the performance loss the larger n becomes. Or using the ID method: SELECT FLOOR(RAND() * MAX(id)) + 1 as n FROM table; SELECT value FROM table id >= n LIMIT 1 This might be the optimal in the two query method. Since it will use the index to seek id >= n. Curt -- If eval() is the answer, you're almost certainly asking the wrong question. -- Rasmus Lerdorf, BDFL of PHP -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
