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

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