Select Random query in MySQL

Question

For several time I use to select random rows as:

$get_question = $user_home->runQuery('SELECT * FROM questions WHERE Level = :Level ORDER BY RAND()');

An expert told me that,

RAND() is a recipe for killing MySQL server !!

So, with the help of this answer I tried:

$get_question = $user_home->runQuery('SELECT * FROM questions AS r1 JOIN (SELECT CEIL(RAND() * (SELECT MAX(Sr) FROM questions)) AS Sr) AS r2 WHERE r1.Sr >= r2.Sr AND Level = :Level ORDER BY r1.Sr ASC LIMIT 1');

And this is how I display the result:

echo $fetch_question['Question'] . "(" . $fetch_question['Id'] . ")";

And the display is:

question(id)

But, sometimes it displays only:

()

Why so? What is the mistake that I did?

Here is the Snapshot of my database having question!!

why dont you just fetch all rows and then select a random row per PHP? — wayneOS, Apr 24 '18 at 14:22
@wayneOS I need only a single row per query, and here the database is having only 15 (5 per `Level`) for understanding, in realistic there 10K+ questions... — PB Musics, Apr 24 '18 at 14:31
If using RAND() was not causing you any problems why change? — Dave, Apr 24 '18 at 14:33
OP has mentioned `RAND() is a recipe for killing MySQL server !!` — Chirag Jain, Apr 24 '18 at 15:05
And yet... if `Rand()` was working, why not use it? The advice from the "Expert" sounds questionable (but I'm not expert, just someone that would use Rand() if it were working for me. — JNevill, Apr 24 '18 at 15:53
@JNevill From my level/point of view, the user who [commented](https://stackoverflow.com/questions/21854184/sql-php-pdo-select-random-row#comment33082649_21854223) is well experienced! — PB Musics, Apr 24 '18 at 16:00
`ORDER BY RAND() LIMIT 11` -- takes milliseconds for a few thousand rows. It takes seconds for a million rows. OK, for a billion rows it is a "killer". Do you have a billion rows? — Rick James, May 07 '18 at 21:35

godot · Answer 1 · 2018-04-25T10:59:17.290

0

I think first of all you should know max and min id's of your table:

select max(id) as mx , min(id) as mn  from questions

then generate random id from PHP:

$randomId = rand($min, $max);

then run your query using this $randomId:

SELECT * FROM questions WHERE id = $randomId

UPDATE:

First of all get whole number of rows:

SELECT COUNT(*) FROM questions

extract it into PHP variable, generate row number between zero and row number:

$randNum = rand(0, $rowCount);

and then run query like that:

SELECT * FROM questions LIMIT $rowNumber, 1

edited Apr 25 '18 at 10:59

answered Apr 25 '18 at 06:32

godot

3,422
6
25
42

1

Equality comparison on ID only works if your records have no gaps. – pritaeas Apr 25 '18 at 07:57
OP has also [commented](https://stackoverflow.com/questions/50004039/select-random-query-in-mysql#comment87024852_50004039), that the table shown is just an example, and OP wants a **random question from a specific `Level`**. If the levels are jumbled it will be difficult to find out the `max` & `min` id. – Chirag Jain Apr 25 '18 at 08:44

score 0 · Answer 2 · answered May 07 '18 at 21:43

If you have under a million rows, ORDER BY RAND() LIMIT nn is not a "killer".

If you still decide that it is a killer, this has multiple ways to get a few random rows without doing a full table scan: http://mysql.rjweb.org/doc.php/random (They go beyond godot's suggestions.)

Select Random query in MySQL

2 Answers2