From: Date: December 1 2005 1:19am
Subject: Re: MySQL 5 is 25% slower then 4.1
List-Archive: http://lists.mysql.com/mysql/192548
Message-Id: <438E41AB.60708@nusconsulting.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chenzhou Cui wrote:

> I didn't use multiple insert statements and "LOAD DATA INFILE", but 
> only "insert into" statement. The Java program reads one line from the 
> source file, and then execute an "insert" statement.

I can't comment on the speed of 5.0.x vs 4.1.x, but I can suggest that 
you optimize this 'insert' process.

Instead of issuing a new insert command per record, how about using 
placeholders and binding values? I don't know any Java, but from Perl 
you can do this. Actually, I'm not certain that MySQL supports this yet 
or not - you'd have to check your server version and drivers.

Or you could do something like collect a series of records - say 100 
records at a time, and issue an insert query that includes all of them, eg:

insert into SomeTable ( field_1, field_2, field_3 ) values
( 3, 56, 45 ),
( 45, 3456, 345 ),
( 345, 76, 345 ),
( 345, 45, 546 )

This is a lot faster than issuing separate inserts per record. You don't 
want to include *too* many records at once - there's a maximum packet 
size or something like that that you can't exceed.

-- 
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@stripped
website: http://www.nusconsulting.com.au