On 24 Apr 12, at 15:57, mysql-digest-help@stripped wrote:
> From: shawn green <shawn.l.green@stripped>
>
>
> 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 ::::
