From: 张志刚
Date: April 20 2012 2:29am
Subject: Why does the limit use the early row lookup.
List-Archive: http://lists.mysql.com/mysql/227212
Message-Id: <20120420022954.2A3CC14209C@smtp-5-43.sina.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0003_01CD1EE0.8769D520"

------=_NextPart_000_0003_01CD1EE0.8769D520
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable

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.=20

Why does the limit use early row lookup but not late row lookup?

For example :=20

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=A3=ACit skips to count off the 1000000 rows by using indexes, and =
count off
10 rows only.

I don=A1=AFt 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

=20

Sincerely yours,

Zhigang zhang


------=_NextPart_000_0003_01CD1EE0.8769D520--