I'll agree to this somewhat: (1) always bind your variables. whatever code you are using to do your inserts, the fewer prepared statements you can make the better.
for example: insert into TableName (col1, col2, col2, col4) values (?,?,?,?) then, once you have a prepared statment, do a loop and insert. (2) drop all indexes on your table(s). rebuild them after loading (this alone can give orders of magnitude improvement) (3) commit every 10,000 records or so. I can load several million rows into our InnoDB tables in a few minutes. good luck jeff Richard Clarke wrote: > > Jeroen, > > Two things are likely to make this umpteen times faster. > > a) Commit the insert transaction every.. say 1000 records? > b) use mysql's extended insert statement, > insert into mytable values > (row1_field1,row1_field2),(row2_field1,row2_field2),(?,?),(?,?) etc etc > > Ric. > > ----- Original Message ----- > From: "Jeroen Geusebroek" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, October 25, 2002 12:11 PM > Subject: Mysql Innodb performance slow > > Hi There, > > We have currently an Interbase Database with millions and millions of > rows which I would like > to migrate to MySQL if possible to increase the speed. > > Transaction support is necessary, so I am using innoDB. > > When inserting 160K rows in the database (in an innoDB table) it takes > about 700! seconds > while the amount of same rows when inserted in a myisam table take about > 100 seconds. > > Now probably this can be fine tuned (I hope), and would like to ask for > some suggestions. > > Is anybody using innodb with this amount of rows? I'm curious of what > the performance is. > > Is there something I should keep in mind when migrating? > > Kind regards, > > Jeroen Geusebroek > > --------------------------------------------------------------------- > 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 > > --------------------------------------------------------------------- > 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 -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 --------------------------------------------------------------------- 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
