List:General Discussion« Previous MessageNext Message »
From:Baron Schwartz Date:February 26 2010 2:33am
Subject:Re: Any faster building primary/unique indexes after Load Data
Infile?
View as plain text  
Hi,

On Sun, Feb 21, 2010 at 1:42 PM, mos <mos99@stripped> wrote:
> I am loading 35 million rows of data into an empty MyISAM table. This table
> has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes.
>
> Is it going to be any faster if I remove the indexes from the table before
> loading the data, load the data, then do an Alter Table .. add index ....
> for all of the indexes?
> Or is it faster to just leave the indexes in place prior to loading the
> data.
>
> I know if the table is empty and optimized, the non-unique indexes will be
> built AFTER the data is loaded using Load Data Infile, but the unique and
> primary indexes will be built as the data is being loaded and this is going
> to slow down the import.
>
> There is no point doing a Disable Indexes on the table because this only
> affects non-unique indexes and that is already taken care of since the table
> is already empty and optimized.
>
> But if I remove the indexes from the empty table then load the data, then
> execute the Alter Table Add Index ... for all 4 indexes at one time, isn't
> the Alter Table going to create a copy of the table so it is just going to
> reload the data all over again?

Yes.  It is going to create a new table, copy the rows into it, and
then delete the old one.

> Is there any way to add a primary or unique index without copy the data all
> over again? Create Index ... can't be used to create a primary index.

Dirty hacks with .frm files and REPAIR TABLE have sometimes been known
to help in cases like this.  But it's not for the faint of heart.
Thread
Any faster building primary/unique indexes after Load DataInfile?mos21 Feb
  • Re: Any faster building primary/unique indexes after Load Data Infile?Carsten Pedersen21 Feb
  • Re: Any faster building primary/unique indexes after Load Data Infile?Baron Schwartz26 Feb
    • Re: Any faster building primary/unique indexes after Load Data Infile?Ananda Kumar26 Feb