Re: speeding up UPDATE and INSERT - Rasika Amarasiri - com.mysql.lists.mysql

10 messages in com.mysql.lists.mysqlRe: speeding up UPDATE and INSERT

From	Sent On	Attachments
McConnell, Ann M.	20 Apr 2004 10:54
Matthias Eireiner	20 Apr 2004 12:24
Paul DuBois	20 Apr 2004 13:05
Matthias Eireiner	20 Apr 2004 13:23
David Griffiths	20 Apr 2004 13:33
Jeremy Zawodny	20 Apr 2004 13:46
Rasika Amarasiri	20 Apr 2004 15:56
McConnell, Ann M.	21 Apr 2004 06:35
vanquish	22 Apr 2004 00:32
David Griffiths	22 Apr 2004 11:49

Subject:	Re: speeding up UPDATE and INSERT
From:	Rasika Amarasiri (rasi...@yahoo.com)
Date:	04/20/2004 03:56:40 PM
List:	com.mysql.lists.mysql

Hi,

I also do a similar thing with my data pre-processing scripts. I use a multiple insert command with INSERT DELAYED. It has improved the performance by heaps. The format is INSERT DELAYED INTO table VALUES (c11, c12, c13), (c21, c22, c23)....;

The INSERT DELAYED will delay the updates so that they get buffered for the updations, but will be done later. Also turning off indexing before you do the insertions will help, but you will need to have them on for updates.

Rasika --- Matthias Eireiner <matt...@ieee.org> wrote: > Hi there,

I'm currently writing a Data Mining program in Perl, my problem is that the writing to the database takes awful long. Are there any ideas how to speed up the inserting and the updating, besides using the bind operator?!

the SQLstatement looks like

"INSERT INTO `Counts` (`id`, `date`, `count`, `avg)VALUES ('', CURDATE(), '$count', '$avg')"

"UPDATE `Counts` SET `count` = '$count', `avg` = '$avg' WHERE `id` = '$id'"

so if anybody has an idea how to get this done faster (dumping the data to a csv file first and updating all at once, or something like this) I would love to hear it. thanks a lot in advance.

regards

Matthias

_________________________________________

Matthias Eireiner

email: matt...@ieee.org

www.bvcapital.com

_________________________________________

-- MySQL Perl Mailing List For list archives: http://lists.mysql.com/perl To unsubscribe:

http://lists.mysql.com/perl?unsub=rasi...@cse.mrt.ac.lk