List:General Discussion« Previous MessageNext Message »
From:Perrin Harkins Date:July 23 2007 6:47pm
Subject:Re: performance of extended insert vs. load data
View as plain text  
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
Thread
performance of extended insert vs. load dataSid Lane23 Jul
  • Re: performance of extended insert vs. load dataPerrin Harkins23 Jul
  • Re: performance of extended insert vs. load datamos23 Jul
    • Re: performance of extended insert vs. load dataPerrin Harkins23 Jul
  • ordering datesRoss Hulford12 Sep
    • Re: ordering datesMichael Dykman12 Sep
    • Re: ordering datesPhilip Hallstrom12 Sep
    • RE: ordering datesJerry Schwartz12 Sep
Re: performance of extended insert vs. load dataB. Keith Murphy23 Jul
  • Re: performance of extended insert vs. load dataMogens Melander24 Jul
  • Re: performance of extended insert vs. load dataRavi Prasad24 Jul