Keith, I would divide the work:
1) CREATE TABLE innodbtypetable ... 2) INSERT INTO innodbtypetable SELECT * from myisamtypetable WHERE primarykey >= xyz AND primarykey < zyx; etc. If you run out of disk space or get some other problem, the rollback will not be as huge as for a single transaction. Regards, Heikki ----- Original Message ----- From: "Keith Thompson" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, January 22, 2004 4:46 PM Subject: Re: InnoDB loading: add keys before or after > Heikki, > > Thanks for your help. > > I have another very large table to convert to InnoDB from MyISAM. > I also have the same table saved in a file suitable for 'load data'. > Which would be faster: > > ALTER TABLE xxx TYPE=InnoDB; > > or > > CREATE TABLE newxxx ...; INSERT INTO newxxx select * from xxx; > > or > > CREATE TABLE newxxx ...; LOAD DATA INFILE ... > > Thanks -keith > > > >Hi! > > >You should always create the indexes BEFORE adding the data to an InnoDB > >table. > > >In CREATE INDEX ... ON ..., MySQL rebuilds the whole table. Thus, it will > be > >much slower to add the indexes afterwards. > > >Many databases have an optimized index build procedure where adding an > index > >afterwards is faster, but that is not the case for InnoDB. > > >DISABLE KEYS has no effect on InnoDB. > > >It is in the TODO to speed up index creation. Maybe in 2005 it will be > >faster to add the indexes afterwards :). > > >Best regards, > > >Heikki Tuuri > >Innobase Oy > >http://www.innodb.com > >Foreign keys, transactions, and row level locking for MySQL > >InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM > >tables > > >Order MySQL technical support from https://order.mysql.com/ > > > > >----- Original Message ----- > >From: "Mirza" <[EMAIL PROTECTED]> > >Newsgroups: mailing.database.myodbc > >Sent: Thursday, January 22, 2004 11:04 AM > >Subject: Re: InnoDB loading: add keys before or after > > > >> In theory it is fastest to add indexes first, then disable it (ALTER > >> TABLE x DISABLE KEYS), then add data and, on the end, re-enable keys > >> (ALTER TABLE x DISABLE KEYS). > >> mirza > >> > >> Keith Thompson wrote: > >> > Hello all, > >> > > >> > I need to load a new InnoDB table with about 80 million rows. > >> > With MyISAM I have often found that it is faster to create > >> > the table without indexes, add all the data, then add the > >> > indexes. Is this true with InnoDB as well, or should I put > >> > my indexes in before loading? > >> > > >> > Similarly, I have another large table that is currently MyISAM > >> > that I'd like to move to InnoDB. What would be the fastest > >> > steps towards accomplishing this (as far as indexes, etc.)? > >> > > >> > Thanks -keith > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
