List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:October 27 2002 2:46pm
Subject:Re: Re: Mysql Innodb performance slow
View as plain text  
sql,query

At 15:50 -0600 10/25/02, Jeff Mathis wrote:
>Paul DuBois wrote:
>>   >(2) drop all indexes on your table(s). rebuild them after loading (this
>>   >alone can give orders of magnitude improvement)
>>
>>   For ISAM or MyISAM, that works.  It has no effect for InnoDB.
>>
>>   Do you actually observe a reproducable difference with this strategy
>>   for InnoDB?
>>
>>   >
>
>Yes I do. I have tables with defined primary key/foreign key
>constraints, which I cannot drop during loading. But dropping the unique
>indexes I have defined on multiple columns does speed up loading times
>substantially.
>
>You say I am not supposed to see this behavior? Can you explain why?

Sure.  I get this from:

http://www.innodb.com/ibman.html#Altering_InnoDB_tables

Which says:

InnoDB does not have a special optimization for separate index creation.
Therefore it does not pay to export and import the table and create
indexes afterwards. The fastest way to alter a table to InnoDB is to do
the inserts directly to an InnoDB table, that is, use ALTER TABLE ...
TYPE=INNODB, or create an empty InnoDB table with identical definitions
and insert the rows with INSERT INTO ... SELECT * FROM ....


>I've been experimenting with putting datafiles on different disks, and
>am now wondering if what I'm seeing is related to disk i/o as opposed to
>insert optimization.
>
>jeff
Thread
Re: Re: Mysql Innodb performance slowPaul DuBois28 Oct
  • BLOB inserts vs. storing filepathJohn Meyer28 Oct
    • Re: BLOB inserts vs. storing filepathMichael T. Babcock28 Oct