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
