http://www.percona.com/ppc2009/PPC2009_mysql_pagination.pdf
Let me know if that is not clear enough. > -----Original Message----- > From: Zhangzhigang [mailto:zzgang_2...@yahoo.com.cn] > Sent: Monday, April 23, 2012 6:56 PM > To: Rick James > Cc: mysql@lists.mysql.com > 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 <rja...@yahoo-inc.com> 写道： > > > 发件人: Rick James <rja...@yahoo-inc.com> > > 主题: RE: Why does the limit use the early row lookup. > > 收件人: "张志刚" <zhig...@leju.sina.com.cn>, "mysql@lists.mysql.com" > > <mysql@lists.mysql.com> > > 日期: 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:zhig...@leju.sina.com.cn] > > > Sent: Thursday, April 19, 2012 7:30 PM > > > To: mysql@lists.mysql.com > > > 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 > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
