From: Jan Steinman Date: April 25 2012 2:31am Subject: Re: Why does the limit use the early row lookup. List-Archive: http://lists.mysql.com/mysql/227246 Message-Id: MIME-Version: 1.0 (Apple Message framework v1084) Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: quoted-printable On 24 Apr 12, at 15:57, mysql-digest-help@stripped wrote: > From: shawn green >=20 >=20 > On 4/22/2012 11:18 PM, Zhangzhigang wrote: >> Why does not the mysql developer team to do this optimization? >=20 > 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 ::::