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.reindl@stripped]
> Sent: Friday, April 20, 2012 12:50 AM
> To: mysql@stripped
> 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 :-)
