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