* Batch INSERTs run faster than one-row-at-a-time, but this is unrelated to
INDEX updating speed.
* The cache size is quite important to dealing with indexing during INSERT; see
http://mysql.rjweb.org/doc.php/memory
* Note that mysqldump sets up for an efficient creation of indexes after
loading the data. This is not practical (or necessarily efficient) when
incremental INSERTing into a table.
As for the original question...
* Updating the index(es) for one row often involves random BTree traversals.
When the index(es) are too big to be cached, this can involve disk hit(s) for
each row inserted.
* By doing all the indexes after building the table (or at least all the
non-UNIQUE indexes), "sort merge" can be used. This technique had been highly
optimized over the past half-century, and is more efficient.
> -Original Message-
> From: Johan De Meersman [mailto:vegiv...@tuxera.be]
> Sent: Monday, May 07, 2012 1:29 AM
> To: Zhangzhigang
> Cc: mysql@lists.mysql.com
> Subject: Re: Why is creating indexes faster after inserting massive
> data rows?
>
> - Original Message -
> > From: "Zhangzhigang"
> >
> > Creating indexes after inserting massive data rows is faster than
> > before inserting data rows.
> > Please tell me why.
>
> 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.
>
> I seem to recall that inside of a transaction (thus, InnoDB or so) the
> difference is markedly less; I might be wrong, though.
>
>
> --
> 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