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]
