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.gr...@oracle.com] > Sent: Monday, April 23, 2012 5:52 AM > To: mysql@lists.mysql.com > Subject: Re: Why does the limit use the early row lookup. > > On 4/22/2012 11:18 PM, Zhangzhigang wrote: > > Why does not the mysql developer team to do this optimization? > > > > --- 12年4月20日，周五, Reindl Harald<h.rei...@thelounge.net> 写道： > > > >> ... > >> > >> 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 :-) > >> > >> > > > > 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? > > 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. > > 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. > > Regards, > -- > Shawn Green > MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware > and Software, Engineered to Work Together. > Office: Blountville, TN > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
