List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:April 22 2010 3:44pm
Subject:Re: Speeding up inserts in InnoDB
View as plain text  
I'm a little confused.. are the inserts slow, or are the updates slow?

It sounds like you mean the updates were going about 50/updates sec. You
could speed up the update by adding an index on phoneticcallsign.CallSign.

JW

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=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
Speeding up inserts in InnoDBChris W22 Apr
  • Re: Speeding up inserts in InnoDBJohnny Withers22 Apr
Re: Speeding up inserts in InnoDBChris W22 Apr