On 24 Apr 12, at 15:57, mysql-digest-h...@lists.mysql.com wrote: > From: shawn green <shawn.l.gr...@oracle.com> > > > On 4/22/2012 11:18 PM, Zhangzhigang wrote: >> Why does not the mysql developer team to do this optimization? > > When the Optimizer is told to sort a result set in the order determined > by a random value created only at the time of the query, what better > technique could they use than to materialize the table, sort the data, > then return the results?
I agree that the common technique of ORDER BY RAND() LIMIT 1 is brain dead in its expectations. And yet, this is a fairly common thing to want. Could not some special syntax be provided to allow for efficient retrieval of random records? I would suggest it belongs in the WHERE clause, so the optimizer would clearly be informed, something like WHERE RANDOM_RECORDS(4) to give four records at random. I have gone so far as to create an indexed field of random numbers, then select on it. But this has its own set of problems, like not being able to guarantee a single result in the selection. Are the standards silent on the need for random selections? ---------------- There are only two ways to look at life: One is as if nothing is a miracle. The other is as if everything is a miracle. -- Albert Einstein :::: Jan Steinman, EcoReality Co-op :::: -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
