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
