List:General Discussion« Previous MessageNext Message »
From:张志刚 Date:April 20 2012 2:29am
Subject:Why does the limit use the early row lookup.
View as plain text  
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


Thread
Why does the limit use the early row lookup.张志刚20 Apr
  • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • RE: Why does the limit use the early row lookup.Rick James20 Apr
      • Re: Why does the limit use the early row lookup.Reindl Harald20 Apr
    • Re: Why does the limit use the early row lookup.Zhangzhigang23 Apr
      • Re: Why does the limit use the early row lookup.shawn green23 Apr
        • RE: Why does the limit use the early row lookup.Rick James23 Apr
  • RE: Why does the limit use the early row lookup.Rick James23 Apr
    • RE: Why does the limit use the early row lookup.Zhangzhigang24 Apr
      • RE: Why does the limit use the early row lookup.Rick James24 Apr
Re: Why does the limit use the early row lookup.Jan Steinman25 Apr