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
– wayneOS, Commented Apr 24, 2018 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... — PBM
– PBM, Commented Apr 24, 2018 at 14:31
If using RAND() was not causing you any problems why change? — Dave
– Dave, Commented Apr 24, 2018 at 14:33
OP has mentioned RAND() is a recipe for killing MySQL server !! — Chirag Jain
– Chirag Jain, Commented Apr 24, 2018 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
– JNevill, Commented Apr 24, 2018 at 15:53

godot · Accepted Answer · 2018-04-25 10:59:17Z

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, 2018 at 10:59

answered Apr 25, 2018 at 6:32

godot

3,5556 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pritaeas Over a year ago

Equality comparison on ID only works if your records have no gaps.

Chirag Jain Over a year ago

OP has also commented, 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.

Rick James · Accepted Answer · 2018-05-07 21:43:32Z

0

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.)

answered May 7, 2018 at 21:43

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Select Random query in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related