Any ORDER BY (that cannot be done using an index) will gather all the data first, then sort, then do the LIMIT.
Potential optimizations include * Keep a "pointer", not the whole data. (This may be practical for SELECT *, but not practical in other cases.) * Build a "priority queue" with only 10 items (in the case of LIMIT 10). The ORDER BY, instead of doing a regular sort, would insert into this queue. This _may_ be more efficient because it would have to hold only 10 rows, not _all_ the rows. > -----Original Message----- > From: Reindl Harald [mailto:h.rei...@thelounge.net] > Sent: Friday, April 20, 2012 12:50 AM > To: mysql@lists.mysql.com > Subject: Re: Why does the limit use the early row lookup. > > > > Am 20.04.2012 04:29, schrieb 张志刚: > > My point is that the limit can use late row lookup: lookup rows after > > checking indexes to optimize the select speed. > > > > But the mysql optimizer do it with the early row lookup: lookup all > > rows before checking indexes when the one fetch column is not in the > indexes. > > > > Tell me why? > > 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 :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
