Fastest way to get random row from mySQL

Asked May 27 '11 at 00:09

Active May 27 '11 at 00:09

Viewed 2,481 times

I have InnoDB table with millions of records. I need to find the way to get random row as fast as i can.

My solution #1 was:

SELECT * FROM keywords T JOIN (SELECT FLOOR(MAX(kid)*RAND()) AS ID) AS x ON T.kid >= x.ID ORDER BY T.kid ASC LIMIT 1

My solution #2 is:

$max = SELECT max(kid) FROM keywords
SELECT * FROM keywords T JOIN (SELECT FLOOR($max*RAND()) AS ID) AS x ON T.kid >= x.ID ORDER BY T.kid ASC LIMIT 1

So, solution #2 is way too faster, but it made of 2 queries. Is there a way to make it in one request ?

asked May 27 '11 at 00:09

artyomboyko

That seems like it could be pretty expensive. – tofutim May 27 '11 at 00:11
9

ORDER BY RAND() will crush server. It's slowest method i know. – artyomboyko May 27 '11 at 00:17
Take a look at this question: http://stackoverflow.com/questions/142242/what-is-the-best-way-to-pick-a-random-row-from-a-table-in-mysql – Doug May 27 '11 at 00:23

0 Answers0