|List:||General Discussion||« Previous MessageNext Message »|
|From:||Alex Schaft||Date:||May 7 2012 8:59am|
|Subject:||Re: 回复： Why is creating indexes fast|
er after inserting massive data rows?
|View as plain text|
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