views:

106

answers:

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?
+1  A: 

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
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
Thats what I mean by gap. The single value returned could hit that gap (that data which does not exist)
JochenJung
+3  A: 

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
Nice idea. Thanks for the code example
JochenJung