0

Pls what query do I need to randomly select JUST ONE ROW in my table?

I have tried:

SELECT * 
FROM `table` 
ORDER BY RAND() 
LIMIT 0,1;

and it seems very slow and sometimes doesnt work.

Thanks

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
Chykere
  • 17
  • 4

2 Answers2

1

If the table is large, ORDER BY will be slow. Instead you can just pick a random offset based on the number of rows in the table.

SELECT * FROM table LIMIT 1 OFFSET ?

Where ? is some random number < SELECT COUNT(*) FROM table

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
1

You can create a STORED PROCEDURE which contains a Dynamic SQL to get random record,

DELIMITER $$
CREATE PROCEDURE SelectOne()
BEGIN
    SET @rownum := (SELECT count(*) FROM TableName);
    SET @start := (SELECT FLOOR((rand() * @rownum)));
    SET @sql = CONCAT('SELECT * FROM TableName LIMIT ?, 1');

    PREPARE stmt FROM @sql;
    EXECUTE stmt USING @start;
    DEALLOCATE PREPARE stmt;        
END $$
DELIMITER ;

thanks to Jack for improvement :)

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Nice implementation of Explosion's answer :) why don't you add this on the mentioned dupe? Though granted, this is only good for selecting exactly one. – Ja͢ck Apr 10 '13 at 07:43
  • 1
    Nice! Though, I would use `FLOOR(RAND() * @rownum)` and have `@rownum` equal to just `count()`, otherwise you starve the first record. http://sqlfiddle.com/#!2/43948/4 – Ja͢ck Apr 10 '13 at 07:55
  • good idea @Jack . let me try that `:)` – John Woo Apr 10 '13 at 07:55
  • Oh, and of course, it should be mentioned that a simple `COUNT(*)` has a different runtime when you use InnoDB :) – Ja͢ck Apr 10 '13 at 08:18
  • 1
    @Jack I added your improvement on the answer `:)` – John Woo Apr 10 '13 at 08:37
  • This worked for me: SELECT * FROM `table` WHERE id >= (SELECT FLOOR( MAX(id) * RAND()) FROM `table` ) ORDER BY id LIMIT 1; – Chykere Apr 11 '13 at 07:58