List:General Discussion« Previous MessageNext Message »
From:David Griffiths Date:February 20 2004 8:12pm
Subject:Re: Speeding up index creation under InnoDB
View as plain text  

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>
Cc: <mysql@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
possible, do
> 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
Speeding up index creation under InnoDBDavid Griffiths20 Feb
  • Re: Speeding up index creation under InnoDBSasha Pachev20 Feb
  • Re: Speeding up index creation under InnoDBDavid Griffiths20 Feb