Re: Retrieve a random row, but fast.

[Jay Paroline]

Hi there,

Hope this isn't too late to help you.
I'm sure you already know that the performance of ORDER BY RAND() LIMIT 1 is terrible for large tables and doesn't scale at all.

MySQL is not too smart and so I believe what is happening the way you are doing the nested select to get the random value is that MySQL is executing that select for every single row it is doing a comparison with. You don't have that problem when you set a variable because comparing with a variable 50,000 times is a lot faster than running a count(*)*rand() 50,000 times.

The trick is to put your nested select in as a nested JOIN. That way it only gets executed once, much faster.

http://wanderr.com/grooveshark/order-by-slow/2008/01/30/

The part you're looking for is this:
SELECT * FROM Table T JOIN (SELECT FLOOR(MAX(ID)*RAND()) AS ID FROM Table) AS x ON T.ID >= x.ID LIMIT 1;