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
