fetch randomly record in php from mysql

Question

I want to fetch randomly record from a mysql table. I know we can fetch using order by rand() but its seems to be slow process for large field. can we have some other way to fetch rand records.

check out this question http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast — Nick, Aug 09 '12 at 06:52

score 2 · Answer 1 · answered Aug 09 '12 at 06:50

2

If you have some primary key Id you can pick a random number between MIN(Id) and MAX(Id) and get that item. Simply try again if the item with that Id does not exist.

answered Aug 09 '12 at 06:50

Sjoerd

74,049
16
131
175

score 2 · Accepted Answer · answered Aug 09 '12 at 06:52

2

You could get a rand offset first by

SELECT FLOOR(RAND() * COUNT(*)) AS rnd FROM your_table

Then select a record by using the offset.

answered Aug 09 '12 at 06:52

xdazz

158,678
38
247
274

score 1 · Answer 3 · edited May 23 '17 at 11:48

1

SELECT name
FROM random JOIN
     (SELECT CEIL(RAND() *
             (SELECT MAX(id)
              FROM random)) AS id
     ) AS r2
     USING (id);

Your question is already answered here:

How can i optimize MySQL's ORDER BY RAND() function?

http://jan.kneschke.de/projects/mysql/order-by-rand/

edited May 23 '17 at 11:48

Community

1
1

answered Aug 09 '12 at 06:52

Omesh

27,801
6
42
51

score 0 · Answer 4 · answered Aug 09 '12 at 06:52

0

You could try this:

SELECT * FROM  table 
join 
  (select round(rand()*(select max(id) from table)) as val from table) as rnd
on rnd.val=table.id;

answered Aug 09 '12 at 06:52

sel

4,982
1
16
22

fetch randomly record in php from mysql

4 Answers4