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] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
