Re: performance of extended insert vs. load data

[Perrin Harkins]

On 7/23/07, mos <mos99@stripped> wrote:
> Load data will of course be much faster. However to obtain the maximum
> speed you need to load the data to an empty table, because then MySQL will
> load the data without updating the index for every row that's added, and
> will instead rebuild the index only after all data has been loaded, which
> of course is much faster.

He's using InnoDB tables, so it doesn't make much difference.  MyISAM
tables have a fast bulk index rebuild, but InnoDB do not.

> The other alternative is to remove all indexes
> prior to using load data and build the index manually when the data has
> been loaded.

The easiest way to do that is with ALTER TABLE DISABLE KEYS and ENABLE
KEYS.  But again, not much help with InnoDB tables.

- Perrin