"Vinay" <[EMAIL PROTECTED]> wrote on 01/31/2006 11:42:51 AM:
> I am using mysql5.0 on Hp-UX. IT took about 14 hours to insert 1.7
> millin records. How do I make my insert run faster.The table has
> three foreign key references and the referencing columns are indexed
> . Is that impacting the insert statement performance.
>
>
> Thanks for the help
> Vinay
Without a doubt, you indexes are slowing you down. MySQL offers the
ability to disable both real-time indexing and real-time foreign key
checking. If this is the parent table of several child tables, you should
be able to safely disable both during your import.
Also, you should try to use the largest extended insert command possible.
Trying to parse and execute 1.7 million individual INSERT statements is a
real pain and you are just flailing your transaction coordinator (open a
tx, do a single insert, commit the tx). With the extended insert format
you get batches of rows per transaction (open a tx, insert several
thousand rows, commit the tx).
Check out:
extended INSERT syntax:
http://dev.mysql.com/doc/refman/5.0/en/insert.html
ALTER TABLE ... ENABLE KEYS/DISABLE KEYS :
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
SET FOREIGN_KEY_CHECKS=... :
http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
http://dev.mysql.com/doc/refman/5.0/en/set-option.html
Speed of INSERTs:
http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
