List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 20 2012 6:26pm
Subject:RE: Why does the limit use the early row lookup.
View as plain text  
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 :-)

Thread
Why does the limit use the early row lookup.张志刚20 Apr
  • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • RE: Why does the limit use the early row lookup.Rick James20 Apr
      • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • Re: Why does the limit use the early row lookup.Zhangzhigang23 Apr
      • Re: Why does the limit use the early row lookup.shawn green23 Apr
        • RE: Why does the limit use the early row lookup.Rick James23 Apr
  • RE: Why does the limit use the early row lookup.Rick James23 Apr
    • RE: Why does the limit use the early row lookup.Zhangzhigang24 Apr
      • RE: Why does the limit use the early row lookup.Rick James24 Apr
Re: Why does the limit use the early row lookup.Jan Steinman25 Apr