List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 24 2012 7:47pm
Subject:RE: Why does the limit use the early row lookup.
View as plain text  
http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf

Let me know if that is not clear enough.

> -----Original Message-----
> From: Zhangzhigang [mailto:zzgang_2008@stripped]
> Sent: Monday, April 23, 2012 6:56 PM
> To: Rick James
> Cc: mysql@stripped
> Subject: RE: Why does the limit use the early row lookup.
> 
> > 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>
> > 主 收件人: "张志刚" <zhigang@stripped>, "mysql@stripped"
> > <mysql@stripped>
> > 日SAM?
> > 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
> > ble 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