List:General Discussion« Previous MessageNext Message »
From:Zhangzhigang Date:April 24 2012 1:55am
Subject:RE: Why does the limit use the early row lookup.
View as plain text  
> If you are doing Pagination via OFFSET and LIMIT --
> Don't.  Instead, remember where you "left off". 
> (More details upon request.)

Thanks for your answer.

Can you tell us the better approach
> about pagination to prevent to scan all table rows? 
How to use "left off"?




---
> 12年4月24日,周二, Rick James <rjames@stripped> 写道:

> 发件人: Rick James <rjames@stripped>
> 主题: RE: Why does the limit use the early row lookup.
> 收件人: "张志刚" <zhigang@stripped>, "mysql@stripped"
> <mysql@stripped>
> 日期: 2012年4月24日,周二,上午2:54
> InnoDB or MyISAM?
> PRIMARY KEY (id) is a separate index in MyISAM, so scanning
> 1000010 rows is faster than for InnoDB, where the PK is
> "clustered" with the data.  That is, MyISAM scans a
> narrow, 2-column, index (id + pointer); InnoDB scans wide
> rows (all columns).
> 
> There is no way to avoid scanning 1000010 rows of something
> (data or index).
> 
> If you are doing Pagination via OFFSET and LIMIT --
> Don't.  Instead, remember where you "left off". 
> (More details upon request.)
> 
> You can trick MySQL into doing "late row lookup" via a "self
> join":
> SELECT b.*
>     FROM tbl a
>     JOIN ( SELECT id FROM  tbl ORDER BY id
> LIMIT 1000000, 10) b
>         ON a.id = b.id
> 
> Meanwhile, see if it is already a feature request at
> bugs.mysql.com .  If not, add it.
> 
> Probably the optimization needs heuristics to decide which
> way to go.  The choice of early vs late may depend on
> all of these:
>   * OFFSET
>   * LIMIT
>   * Number of rows in the table
>   * Width of the table versus width of the key
> involved.
> 
> > -----Original Message-----
> > From: 张志刚 [mailto:zhigang@stripped]
> > Sent: Thursday, April 19, 2012 7:30 PM
> > To: mysql@stripped
> > Subject: Why does the limit use the early row lookup.
> > 
> > Dear all:
> > 
> > I encounted a question that the limit is not use index
> to lookup row
> > when I issue a sql.
> > 
> > Theoretically, the lock is used when the sql update
> table data and
> > update table indexes, It ensures updating data and
> updating indexes are
> > synchronous.
> > 
> > Why does the limit use early row lookup but not late
> row lookup?
> > 
> > For example :
> > 
> > Create table test (id int primary key, name char(20));
> > 
> > select * from test order by id limit 1000000, 10.
> > 
> > The above sql is very slow when one fetch column is not
> in the indexes
> > and the offset is more than million.
> > 
> > The above sql count off the 1000010 rows and return the
> top 10 rows.
> > 
> > But the sql: select id from test order by id limit
> 1000000, 10 is very
> > fast,it skips to count off the 1000000 rows by using
> indexes, and
> > count off
> > 10 rows only.
> > 
> > I don’t know the reason.
> > 
> > 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?
> > 
> > Thanks
> > 
> > 
> > 
> > Sincerely yours,
> > 
> > Zhigang zhang
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql

> To unsubscribe:    http://lists.mysql.com/mysql

> 
> 
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