Sasha, Thanks for the reply. It's actually during index creation (while creating a database). I am creating the tables, loading the data, then applying the indexes and foreign keys.
There are five or six indexes on some tables, the first taking 5 minutes, the second taking 5:45, etc. In this case, is the first index still being rebuilt when the second index is added? My guess was that there was re-organization being done in the tablespace for each existing index, and thus each additional index took even longer. The tablespace is on the disk, and therefore a faster disk would help. David. ----- Original Message ----- From: "Sasha Pachev" <[EMAIL PROTECTED]> To: "David Griffiths" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, February 20, 2004 8:41 AM Subject: Re: Speeding up index creation under InnoDB > David Griffiths wrote: > > I was wondering what the bottleneck was. I'm adding a dozen indexes to > > the same large-ish InnoDB table. Each successive index takes a bit > > longer (45 seconds or so on a dual P3-933 with 2 gig of RAM). > > Every time you add a new index or do any non-trivial modification to the schema, > the old ones are being re-created. Because of that, you should , if possible, do > all schema modifications at once ( eg alter table add key(col1),add key(col2), > add n int not null instead of alter table add key(col1); alter table add > key(col2); alter table add n int not null) > > > > Is it disk additional tables-space management that is taking the extra > > time? Would faster disks help? > > As a rule of thumb, when you feel tempted to add a faster disk to a MySQL > server, you should resist the temptation. In three years of working on the MySQL > support team I do not recall ever recommending to buy a faster disk - we've > always been able to find a more elegant solution. > > -- > Sasha Pachev > Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
