Re: Why does the limit use the early row lookup.

2012-04-24 Thread Jan Steinman
On 24 Apr 12, at 15:57, mysql-digest-h...@lists.mysql.com wrote: > From: shawn green > > > On 4/22/2012 11:18 PM, Zhangzhigang wrote: >> Why does not the mysql developer team to do this optimization? > > When the Optimizer is told to sort a result set in the order determined > by a random val

RE: Why does the limit use the early row lookup.

2012-04-24 Thread Rick James
tter approach about pagination to prevent to scan > all table rows? > How to use "left off"? > > > > > --- 12年4月24日，周二, Rick James 写道： > > > 发件人: Rick James > > 主题: RE: Why does the limit use the early row lookup. > > 收件人: "张志刚&quo

RE: Why does the limit use the early row lookup.

2012-04-23 Thread Zhangzhigang
eft off"? --- 12年4月24日，周二, Rick James 写道： > 发件人: Rick James > 主题: RE: Why does the limit use the early row lookup. > 收件人: "张志刚" , "mysql@lists.mysql.com" > > 日期: 2012年4月24日,周二,上午2:54 > InnoDB or MyISAM? > PRIMARY KEY (id) is a separate index in My

RE: Why does the limit use the early row lookup.

2012-04-23 Thread Rick James
ort is nearly Order(1), and the memory is only slightly more than the cost of 10 rows. > -Original Message- > From: shawn green [mailto:shawn.l.gr...@oracle.com] > Sent: Monday, April 23, 2012 5:52 AM > To: mysql@lists.mysql.com > Subject: Re: Why does the limit use the ea

RE: Why does the limit use the early row lookup.

2012-04-23 Thread Rick James
InnoDB or MyISAM? PRIMARY KEY (id) is a separate index in MyISAM, so scanning 110 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 scanni

Re: Why does the limit use the early row lookup.

2012-04-23 Thread shawn green
On 4/22/2012 11:18 PM, Zhangzhigang wrote: > Why does not the mysql developer team to do this optimization? > > --- 12年4月20日，周五, Reindl Harald 写道： > >> ... >> >> because the mysql optimizer until now is really >> bad in many situations - order by rand() makes a >> temporary table wil ALL data as

Re: Why does the limit use the early row lookup.

2012-04-22 Thread Zhangzhigang
Why does not the mysql developer team to do this optimization? --- 12年4月20日，周五, Reindl Harald 写道： > 发件人: Reindl Harald > 主题: Re: Why does the limit use the early row lookup. > 收件人: mysql@lists.mysql.com > 日期: 2012年4月20日,周五,下午3:50 > > > Am 20.04.2012 04:29, schrieb 张志刚:

Re: Why does the limit use the early row lookup.

2012-04-20 Thread Reindl Harald
ld [mailto:h.rei...@thelounge.net] >> Sent: Friday, April 20, 2012 12:50 AM >> To: mysql@lists.mysql.com >> Subject: Re: Why does the limit use the early row lookup. >> >> >> >> Am 20.04.2012 04:29, schrieb 张志刚: >>> My point is that the limit can

RE: Why does the limit use the early row lookup.

2012-04-20 Thread Rick James
lto:h.rei...@thelounge.net] > Sent: Friday, April 20, 2012 12:50 AM > To: mysql@lists.mysql.com > Subject: Re: Why does the limit use the early row lookup. > > > > Am 20.04.2012 04:29, schrieb 张志刚: > > My point is that the limit can use late row lookup: lookup rows af

Re: Why does the limit use the early row lookup.

2012-04-20 Thread Reindl Harald
Am 20.04.2012 04:29, schrieb 张志刚: > 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 th