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
