List:General Discussion« Previous MessageNext Message »
From:Kishore Jalleda Date:January 31 2006 5:25pm
Subject:Re: Insert performance
View as plain text  
the indexes would significantly slow down the inserts, also are you doing
the insert from a sql script, if so you couldtry this
Wrapping your very long insert script in as follows

set autocommit = 0 ;
begin ;

[[thousands of inserts here]]

commit ;


If you use the *mysqldump* option --opt, you get dump files that are fast to
import into an InnoDB table, even without wrapping them with the SET
AUTOCOMMIT and COMMIT statements

you could also try this
SET FOREIGN_KEY_CHECKS=0;
*... import operation ...*
SET FOREIGN_KEY_CHECKS=1;

This would speed up your table inserts by turning the foreign key checks for
the duration of the import session...

you could also may be tune your blk_insert_buffer_size

Kishore Jalleda


On 1/31/06, Vinay <vinay@stripped> wrote:
>
> 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
>

Thread
Insert performanceVinay31 Jan
  • Re: Insert performanceKishore Jalleda31 Jan
  • Re: Insert performanceDavid Griffiths31 Jan
  • Re: Insert performanceSGreen31 Jan
  • Re: Insert performanceImran Chaudhry31 Jan
    • Re: Insert performanceSGreen31 Jan
  • Re: Insert performanceAdy Wicaksono1 Feb
Re: Insert performanceHeikki Tuuri5 Feb