Hi,

A couple cents to this.

There isn't really a million of block writes. The record gets added to the block, but that gets modified in OS cache if we assume MyISAM tables and in the Innodb buffer if we assume InnoDB tables. In both cases, the actual writing does not take place and does not slow down the process. What does however happen for each operation, is processing the statement, locating the entries to update in the index, index block splits and , for good reason, committing.

When it comes to creating an index, what needs to happen, is to read the whole table and to sort all rows by the index key. The latter process will be the most determining factor in answering the original question, because for the large tables the sort will have to do a lot of disk I/O. The point I am trying to make is there will be situations when creating indexes and then inserting the rows will be faster than creating an index afterwards. If we try to determine such situations, we could notice that the likelihood of the sort going to disk increases with the amount of distinct values to be sorted. For this reason, my choice would be to create things like primary/unique keys beforehand unless I am certain that everything will fit in the available memory.

Peace
Karen



On May 7, 2012, at 8:05 AM, Johan De Meersman wrote:

> ----- Original Message -----
>
>> From: "Zhangzhigang" <zzgang_2008@yahoo.com.cn>
>
>> 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?
> No, when you create an index on an existing table (like after a mass insert), what happens is that the engine does a single full tablescan and builds the index in a single pass, which is a lot more performant than updating a single disk block for every record, for the simple reason that a single disk block can contain dozens of index entries.
>
> Imagine that you insert one million rows, and you have 100 index entries in a disk block (random numbers, to make a point. Real numbers will depend on storage, file system, index, et cetera). Obviously there's no way to write less than a single block to disk - that's how it works.
>
> You can update your index for each record in turn. That means you will need to do 1 million index - and thus block - writes; plus additional reads for those blocks you don't have in memory - that's the index cache.
>
> Now, if you create a new index on an existing table, you are first of all bypassing any index read operations - there *is* no index to read, yet. Then the system is going to do a full tablescan - considered slow, but you need all the data, so there's no better way anyway. The index will be built - in-memory as much as possible - and the system will automatically prefer to write only complete blocks - 10.000 of them. That's the exact same number of index blocks, but you only write each block once, so that's only 10.000 writes instead of 1.000.000.
>
> Now there's a lot more at play, things like B-tree balancing and whatnot, but that's the basic picture.
>
> --
>
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql