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
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
