List:General Discussion« Previous MessageNext Message »
From:Rick James Date:April 23 2012 6:54pm
Subject:RE: Why does the limit use the early row lookup.
View as plain text  
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:zhigang@stripped]
> 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

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