List:General Discussion« Previous MessageNext Message »
From:mos Date:February 21 2010 6:42pm
Subject:Any faster building primary/unique indexes after Load Data
Infile?
View as plain text  
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?

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.

TIA
Mike

MySQL 5.1

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