too nice not to share it! http://www.youtube.com/watch?v=INHF_5RIxTE
2012/5/7 Zhangzhigang <zzgang_2...@yahoo.com.cn> > Thanks, i thought about this answer in the past, and i appreciate your > reply. > > > > ________________________________ > 发件人： Alex Schaft <al...@quicksoftware.co.za> > 收件人： mysql@lists.mysql.com > 发送日期： 2012年5月7日, 星期一, 下午 4:59 > 主题: Re: 回复： Why is creating indexes faster after inserting massive data > rows? > > On 2012/05/07 10:53, Zhangzhigang wrote: > > 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. > My simplified 2c. When inserting rows with active indexes one by one > (insert), mysql has to > > 1) lock the space for the data to be added, > 2) write the data, > 3) lock the index, > 4) write the index key(s), > 5) unlock the index, > 6)unlock the data > > This happens for each row > > When first doing all data without index, only 1, 2, and 6 happen. When you > then create an index, it can lock the index, read all the data and write > all index keys in one go and then unlock the index. > > If you make an omelet, do you fetch your eggs from the fridge one by one, > or all at the same time? :) > > HTH, > Alex > > > -- MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql > -- Claudio
