Multiple rows alternative for RAND()

Question

I am having problems with my query for selecting 100 random rows from a database with 50k of rows.

First i only used the function RAND() but that became too slow.

Now i am using this query:

$query = mysql_query("SELECT t1.* FROM users AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM users WHERE picture != '' && age <= 50 && age >= 18 && username != '')) AS id) AS t2 WHERE picture != '' && age <= 50 && age >= 18 && username != '' && t1.id >= t2.id LIMIT 100");
while($q = mysql_fetch_assoc($query)) 
{ echo $q['id'].'<br>'; }

But sometimes it gives a bunch of empty rows.

Can anybody tell me whats going wrong?

Thanks!

UPDATE I am trying this query too but it gives me an error.

$query = mysql_query("SELECT id FLOOR(1 + RAND() * x.m_id) 'rand_ind' FROM users (SELECT MAX(t.id) - 1 'm_id' FROM users t) x WHERE picture != '' && age <= 50 && age >= 18 && username != '' ORDER BY rand_ind LIMIT 100");

mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

Getting all the rows in any order, then shuffling it in PHP (or taking a few random ones from the resulting array) might be the correct approach. — Madara's Ghost, May 31 '13 at 08:24
@MadaraUchiha, selecting 50k rows when we actually need only 100 isn't a very good idea — Vladimir Posvistelik, May 31 '13 at 08:31

score 1 · Answer 1 · answered May 31 '13 at 08:27

Randomise the order with RAND() and then just select limit 0,100

order by rand() limit 0,100

Fastest way of doing it in mysql

You may want to read this for alternatives http://jan.kneschke.de/projects/mysql/order-by-rand/ There are some limitations to doing it the ways suggested in that article though

Damien Overeem · Answer 2 · 2013-05-31T12:17:12.513

1

If your id's in the table are sequential, why not just generate x random numbers between 0 and "max_rows".

Then do something like

'SELECT ... FROM .... WHEREidin (' . implode(',', $randomNumbers) . ')';

It will return the rows with the generated id's.

edited May 31 '13 at 12:17

answered May 31 '13 at 08:33

Damien Overeem

4,487
4
36
55

Stephan · Answer 3 · 2013-05-31T08:36:44.743

-1

You could use :

SELECT ... FROM ... LIMIT $rand,100

Where $rand is a random generated number (0-50k) from code side

edited May 31 '13 at 08:36

answered May 31 '13 at 08:26

Stephan

8,000
3
36
42

2

Unfortunately, only the first item would be random, the rest is quasi random at best. – Ja͢ck May 31 '13 at 08:27
yes , the group of 100 rows is random not each row – Stephan May 31 '13 at 08:37
anyway if the limit would be 2000,100 then not all of the next 100 users have the filters that i use in `"WHERE picture != '' && age <= 50 && age >= 18 && username != ''"` – user2370950 May 31 '13 at 08:41
the `LIMIT` applies AFTER the filtering NOT before – Stephan May 31 '13 at 08:54

Multiple rows alternative for RAND()

3 Answers3

Linked