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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
