Ok, but my opinion is that the sorting algorithms is not impact this difference, two ways all do B+ tree inserts.
________________________________ 发件人： Claudio Nanni <claudio.na...@gmail.com> 收件人： Zhangzhigang <zzgang_2...@yahoo.com.cn> 抄送： Johan De Meersman <vegiv...@tuxera.be>; "mysql@lists.mysql.com" <mysql@lists.mysql.com> 发送日期： 2012年5月7日, 星期一, 下午 5:01 主题: Re: 回复： Why is creating indexes faster after inserting massive data rows? Creating the index in one time is one macro-sort operation, updating the index at every row is doing the operation on and on again. If you do not understand the difference I recommend you to read some basics about sorting algorithms, very interesting read anyway. Claudio 2012/5/7 Zhangzhigang <zzgang_2...@yahoo.com.cn> johan .... > >>Plain and simple: the indices get updated after every insert statement, >whereas if you only create the index *after* the inserts, the index gets >created in a single operation, which is a lot more efficient.. > > >Ok, Creating the index *after* the inserts, the index gets created in a single >operation. >But the indexes has to be updating row by row after the data rows has all been >inserted. Does it work in this way? >So i can not find the different overhead about two ways. > > > > > >________________________________ > 发件人： Johan De Meersman <vegiv...@tuxera.be> > >收件人： Zhangzhigang <zzgang_2...@yahoo.com.cn> >抄送： mysql@lists.mysql.com >发送日期： 2012年5月7日, 星期一, 下午 4:28 > >主题: Re: Why is creating indexes faster after inserting massive data rows? > > >----- Original Message ----- >> From: "Zhangzhigang" <zzgang_2...@yahoo.com.cn> >> >> Creating indexes after inserting massive data rows is faster than >> before inserting data rows. >> Please tell me why. > >Plain and simple: the indices get updated after every insert statement, >whereas if you only create the index *after* the inserts, the index gets >created in a single operation, which is a lot more efficient. > >I seem to recall that inside of a transaction (thus, InnoDB or so) the >difference is markedly less; I might be wrong, though. > > >-- >Bier met grenadyn >Is als mosterd by den wyn >Sy die't drinkt, is eene kwezel >Hy die't drinkt, is ras een ezel -- Claudio
