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
