MySQL Forums
Forum List  »  General

random sorting - what is the quickest method?
Posted by: nick rulez
Date: February 04, 2011 09:26AM

Hallo guys. I'd like to have some advice about the best way to get random record(s).

This is the method I'm using and the fastest I know:

set @count = (select count(*) from table);
set @offset = convert(floor(rand() * @count), signed);
prepare mystatement from "select sql_no_cache * from table limit ?, 1";
execute mystatement using @offset;
deallocate prepare mystatement;

However it requires a select count(*) and this is a problem with innodb tables.
Moreover if I want to extract more than one record, it could occur that the query returns me less record than the number I specify in the offset.

Thanks in advance.

Options: ReplyQuote

Written By
random sorting - what is the quickest method?
February 04, 2011 09:26AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.