> 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
