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.