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
----- Original Message -----
From: "Sasha Pachev" <sasha@stripped>
To: "David Griffiths" <dgriffiths@stripped>
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
> the old ones are being re-created. Because of that, you should , if
> all schema modifications at once ( eg alter table add key(col1),add
> 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
> support team I do not recall ever recommending to buy a faster disk -
> always been able to find a more elegant solution.
> Sasha Pachev
> Create online surveys at http://www.surveyz.com/