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

--00504502cc4947684c04807c09cd
Content-Type: text/plain; charset=ISO-8859-1

Then before loading
alter table table_name disable keys;
load data
alter table table enable keys;

This will enable faster data load and faster index rebuild.

regards
anandkl

On Fri, Feb 26, 2010 at 8:03 AM, Baron Schwartz wrote:

> 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=anandkl@gmail.com
>
>

--00504502cc4947684c04807c09cd--