Very slow to insert rows to InnoDB 1

[dadainiao]

I am using 4.017 version of mysql, and created a fully independent test table like
CREATE TABLE tickets (
TicketID bigint(20) NOT NULL auto_increment,
TicketNumber int(11) NOT NULL default '0',
DateTimeID datetime NOT NULL default '0000-00-00 00:00:00',
GameID smallint(6) NOT NULL default '0',
CashIn varchar(100) NOT NULL default '',
CashOut varchar(100) NOT NULL default '',
PRIMARY KEY (`TicketID`)
) TYPE=MyISAM;

When It was set to type InnoDB, insert 1000 rows takes nearly 30 seconds! But with MyISAM, only a little more than half second. How could different be that big?
Any help very be greatly appreciated.

 

[sleipnir214]

How are you inserting the records?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[dadainiao]

Insert into tickets values(null,0,'2003-11-1 00:00:00',7,'8.8','2.3')

It is just a test. loop 1000 times.

 

[MartijnTonies]

Try starting a transaction BEFORE the loop and COMMIT it AFTER the loop. This will insert all rows in a single transaction, that should make a difference, I guess.

Martijn Tonies
Database Workbench - the developer tool for InterBase, Firebird, MySQL & MS SQL Server

http://www.upscene.com

 

[cdlvj]

Not positive of the syntax but this will speed it up. Do one insert and many values. You will need to look up the correct syntax.
Set autocommit off, and do commits after several hundred.

Insert into tickets values(null,0,'2003-11-1 00:00:00',7,'8.8','2.3'),
(null,0,'2003-11-1 00:00:00',7,'8.8','2.3'),
(null,0,'2003-11-1 00:00:00',7,'8.8','2.3'),
(null,0,'2003-11-1 00:00:00',7,'8.8','2.3'),
(null,0,'2003-11-1 00:00:00',7,'8.8','2.3')

 

[sleipnir214]

What I would recommend, if possible, is to load the data using a LOAD DATA INFILE query:

http://www.mysql.com/doc/en/LOAD_DATA.html

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[dadainiao]

I tried MartijnTonies's method. It works great. Take less than half second to insert 1000 rows. cdlvj's advise should also works.
sleipnir214, I was not using extern file, so I did not try your method.

Thanks all for your good advises.