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 <ba...@xaprb.com> wrote: > Hi, > > On Sun, Feb 21, 2010 at 1:42 PM, mos <mo...@fastmail.fm> 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=anan...@gmail.com > >
