From: Chris W
Date: April 22 2010 3:49pm
Subject: Re: Speeding up inserts in InnoDB
List-Archive: http://lists.mysql.com/mysql/221386
Message-Id: <4BD06FFA.8050604@cox.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sorry I misspoke, I am doing updates not inserts.  If I was doing 
inserts I thought about the multiple record at a time idea but unless 
there is something I don't know, I don't think you can do that with 
updates.  I will look into turning autocommit off and see what that does.

Chris W.

Andrew Carlson wrote:
> If you are doing batch inserts, either turn autocommit off, and commit
> after every so many inserts, or use the multiple values insert
> statement to insert multiple records at one time.  If the inserts are
> coming from multiple sources/processes, it's a little bit of a harder
> problem.
>
> On Thu, Apr 22, 2010 at 10:13 AM, Chris W <4rfvgy7@stripped> wrote:
>   
>> I have a very simple table.
>>
>> CREATE TABLE  `hams`.`phoneticcallsign` (
>>  `CallSign` char(6) NOT NULL,
>>  `PhoneticCallSign` char(6) NOT NULL,
>>  PRIMARY KEY (`CallSign`),
>>  KEY `PhoneticIdx` (`PhoneticCallSign`) USING BTREE
>> )
>> I inserted a little over 1 million records with CallSign = to a value from
>> another table and PhoneticCallSign blank.  Then I used the following simple
>> php script to set the value of PhoneticCallSign.
>>
>>  $query  = "SELECT `CallSign` \n";
>>  $query .= "FROM `phoneticcallsign`  \n";
>>  $query .= "WHERE `PhoneticCallSign` = '' \n";
>>  $result = mysql_query($query) or die("DB error $query" . mysql_error() );
>>  while(($row = mysql_fetch_row($result))){
>>   $CallSign = $row[0];
>>   $PhoneticCallSign = SoundsLike($CallSign);
>>   $query  = "UPDATE `phoneticcallsign` \n";
>>   $query .= "SET `PhoneticCallSign` = '$PhoneticCallSign' \n";
>>   $query .= "WHERE `CallSign` = '$CallSign' \n";
>>   $Uresult = mysql_query($query) or die("DB error $query" . mysql_error() );
>>  }
>>
>> This was running very slow and I was getting only about 50 inserts per
>> second.  I noticed that the table was InnoDB so I decided to change it to
>> MyISAM and try again.  With MyISAM I was getting around 10,000 inserts per
>> second.  Surely there is some way to make InnoDB faster.
>>
>> Any ideas?
>>
>> Chris W
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=naclosagc@stripped
>>
>>
>>     
>
>
>
>