List:General Discussion« Previous MessageNext Message »
From:Carsten Pedersen Date:February 21 2010 10:12pm
Subject:Re: Any faster building primary/unique indexes after Load Data
Infile?
View as plain text  
Generally, you should find that removing and re-adding the indexes will 
speed up your operation.

I do not believe that ALTER TABLE with just index additions will require 
a table rebuild, but even if it does, doing a table copy will be a 
fairly fast operation (much faster than loading from other sources).

Don't forget to set the MyISAM sort buffer size high while you create 
the indexes.

/ Carsten

mos skrev:
> 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