List:General Discussion« Previous MessageNext Message »
From:Jan Steinman Date:April 25 2012 2:31am
Subject:Re: Why does the limit use the early row lookup.
View as plain text  
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 ::::




Thread
Why does the limit use the early row lookup.张志刚20 Apr
  • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • RE: Why does the limit use the early row lookup.Rick James20 Apr
      • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • Re: Why does the limit use the early row lookup.Zhangzhigang23 Apr
      • Re: Why does the limit use the early row lookup.shawn green23 Apr
        • RE: Why does the limit use the early row lookup.Rick James23 Apr
  • RE: Why does the limit use the early row lookup.Rick James23 Apr
    • RE: Why does the limit use the early row lookup.Zhangzhigang24 Apr
      • RE: Why does the limit use the early row lookup.Rick James24 Apr
Re: Why does the limit use the early row lookup.Jan Steinman25 Apr