random record from mysql table

Question

Is there any built-in function except order by random() to select a random record in mysql table?

Do you have any certain problem at the moment with your tips and order by random()? — Your Common Sense, Jun 11 '10 at 05:10
See similar questions: http://stackoverflow.com/questions/1244555/how-can-i-optimize-mysqls-order-by-rand-function http://stackoverflow.com/questions/1823306/alerternative-to-mysql-order-by-rand http://stackoverflow.com/questions/1868102/order-by-rand-alternative http://stackoverflow.com/questions/2882647/mysql-order-by-rand-name-asc http://stackoverflow.com/questions/2663710/how-does-mysqls-order-by-rand-work — nickf, Jun 11 '10 at 05:14

score 2 · Answer 1 · answered Jun 11 '10 at 04:50

2

No, but you can make it into two questions. In pseudo-PHP-and-MySQL-code:

$max = SELECT COUNT(*) FROM example;
$rand = rand(0, $max-1);
SELECT * FROM example LIMIT $rand, 1;

The right way would probably be to make it into a stored procedure.

answered Jun 11 '10 at 04:50

Emil Vikström

Thanks for your immediate response. Actually i dont want to run two select queries as i am using this for displaying tips for user randomly. Is there any other idea? – shivg Jun 11 '10 at 04:59
@rshivaganesh - this method has *much* better performance than `order by rand()` – nickf Jun 11 '10 at 05:10
Not that I'm aware of. I would go with ORDER BY RAND() on a small table. Maybe you can cache the result from the first query in some way? – Emil Vikström Jun 11 '10 at 05:11
sure. `$rand = rand(0, $max-$num); LIMIT $rand, $num;` ;) – Your Common Sense Jun 11 '10 at 05:18

score 1 · Answer 2 · answered Jun 11 '10 at 04:49

1

I don't think so... why don't you want to use the one that works?

BTW, I'm pretty sure it is ORDER BY RAND().

I've read it can be a performance problem on many rows... do you have many rows?

answered Jun 11 '10 at 04:49

alex

no,i have about 1000 tips but i have around 10000 users, it will slow down the process and also the tips are based on category. – shivg Jun 15 '10 at 04:55

Salil · Answer 3 · 2010-06-11T06:44:22.887

1

SELECT * FROM users ORDER BY RAND() Limit 0, 1;

edited Jun 11 '10 at 06:44

answered Jun 11 '10 at 05:08

Salil

that should be `LIMIT 0, 1` or `LIMIT 1` – nickf Jun 11 '10 at 05:12
1

@nickf does it *really* matter? ;-) – Your Common Sense Jun 11 '10 at 05:16
@nickf :- I edit it but i think 'Limit 1, 1;' also not wrong as he want random data to display. – Salil Jun 11 '10 at 06:46
what if there's only one row..? – nickf Jun 12 '10 at 07:36
that's why i edit it but yes then order by rand() will always result same row :) – Salil Jun 12 '10 at 10:57
Yes, order by rand() will always result one from some set of records – shivg Jun 14 '10 at 05:14

3 Answers3