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:
- How can I use RAND() within LIMIT?
- Do you know of any other way to solve this with just one query?