Re: Any faster building primary/unique indexes after Load Data

Hi,

On Sun, Feb 21, 2010 at 1:42 PM, mos 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.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org