From: Rick James
Date: April 23 2012 7:00pm
Subject: RE: Why does the limit use the early row lookup.
List-Archive: http://lists.mysql.com/mysql/227234
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB1485A7D8E9@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-2022-jp"
Content-Transfer-Encoding: quoted-printable

Shawn...

ORDER BY RAND() LIMIT 10
Also assuming:
  Table >> 10 rows
  MEMORY is practical for tmp table in this case

Here's a faster way:
Keep an in-RAM "priority queue", truncating it at 10 items.  Simply insert =
rows into it as you walk through the unsorted table.  The effort is nearly =
Order(1), and the memory is only slightly more than the cost of 10 rows.

> -----Original Message-----
> From: shawn green [mailto:shawn.l.green@stripped]
> Sent: Monday, April 23, 2012 5:52 AM
> To: mysql@stripped
> Subject: Re: Why does the limit use the early row lookup.
>=20
> On 4/22/2012 11:18 PM, Zhangzhigang wrote:
> > Why does not the mysql developer team to do this optimization?
> >
> > --- 12=1B$BG/=1B(B4=1B$B7n=1B(B20=1B$BF|!$<~8^=1B(B, Reindl Harald<h.re=
indl@stripped>  =1B$B<LF;!'=1B(B
> >
> >> ...
> >>
> >> because the mysql optimizer until now is really bad in many
> >> situations - order by rand() makes a temporary table wil ALL data as
> >> example even with limit
> >>
> >> select * from table order by rand() limit 10; reads and writes the
> >> whole table to disk have fun with large tables :-)
> >>
> >>
> >
>=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?
>=20
> If you can think of a better way of sorting random numbers, please tell
> us. MySQL has and still does accept solutions from the community. I do
> admit that at times in our past we have been very slow about processing
> those submissions but recently we have made great improvements in how
> we handle those.
>=20
> Also, we have made significant strides in improving our Optimizer in
> 5.5 and more improvements will be coming in 5.6.  Please check out our
> newer versions to see if we have solved or improved any particular
> scalability problems you may be having.
>=20
> Regards,
> --
> Shawn Green
> MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware
> and Software, Engineered to Work Together.
> Office: Blountville, TN
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql