MySQL Forums :: General :: random sorting - what is the quickest method?


Advanced Search

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


Subject Written By Posted
random sorting - what is the quickest method? nick rulez 02/04/2011 09:26AM
Re: random sorting - what is the quickest method? nick rulez 02/04/2011 10:42AM
Re: random sorting - what is the quickest method? Rick James 02/05/2011 02:37PM
Re: random sorting - what is the quickest method? nick rulez 02/12/2011 08:04AM
Re: random sorting - what is the quickest method? Rick James 02/12/2011 12:34PM
Re: random sorting - what is the quickest method? Ilies Radu 02/16/2011 03:05AM


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.