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.
Subject
Written By
Posted
random sorting - what is the quickest method?
February 04, 2011 09:26AM
February 04, 2011 10:42AM
February 05, 2011 02:37PM
February 12, 2011 08:04AM
February 12, 2011 12:34PM
February 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.