2

Using RAND() in MySQL to get a single random row out of a huge table is very slow:

SELECT quote FROM quotes ORDER BY RAND() LIMIT 1

Here is an article about this issue and why this is the case.

Their solution is to use two queries:

SELECT COUNT(*) AS cnt FROM quotes

- Use result to generate a number between 0 and COUNT(*)

SELECT quote FROM quotes LIMIT $generated_number, 1

I was wondering, whether this would be possible in just one query.

So my approach was:

SELECT * FROM quotes
LIMIT (
  ROUND(
    (SELECT COUNT(*) FROM quotes) * RAND()
  )
), 1

But it seams MySQL does not allow any logic within Limit. Though I can not find any information about this topic, whether this is true.

So my Questions:

  1. How can I use RAND() within LIMIT?
  2. Do you know of any other way to solve this with just one query?
JochenJung
  • 7,183
  • 12
  • 64
  • 113
  • possible duplicate of [MySQL select 10 random rows from 600K rows fast](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast) – Peter O. Sep 15 '14 at 11:39
  • That other question was posted later, than mine :-) Though it is a duplicate, yes. – JochenJung Sep 15 '14 at 11:48

3 Answers3

5

Is there a reason why a stored procedure cannot be used to create a prepared statement?

DELIMITER //
DROP PROCEDURE IF EXISTS rand_quote//
CREATE PROCEDURE rand_quote()
BEGIN
    SET @rand := ROUND((SELECT COUNT(*) FROM quotes) * RAND());
    SET @sql := CONCAT('SELECT * FROM quotes LIMIT ', @rand, ', 1');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END;
//
DELIMITER ;
Mike
  • 21,301
  • 2
  • 42
  • 65
1

I solved the problem by checking max id. Then i made php loop of rand(0, max_id) which checks if object exists. Done.

Much faster then previous ordering by rand.

Roman Losev
  • 1,911
  • 19
  • 26
1

I just figgured this one, that seams like a solution:

SELECT * FROM quotes
WHERE quotes_id = ROUND(
  (SELECT COUNT(*) FROM quotes) * RAND()
)
LIMIT 1

But it will work only if quotes_id has no gaps.

JochenJung
  • 7,183
  • 12
  • 64
  • 113
  • What do gaps matter if RAND() produces a random number? My concern is the randomized portion will return a single value, and there's a risk that the value the random portion returns doesn't exist in your data. – OMG Ponies Jul 22 '10 at 17:59
  • Thats what I mean by gap. The single value returned could hit that gap (that data which does not exist) – JochenJung Jul 22 '10 at 18:32
  • Using "LIMIT 1" is useless here since there is a filter on quotes_id which is the primary key: it will never return more than one record. – Jocelyn Mar 20 '13 at 12:01