Speeding up inserts in InnoDB

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=gcdmg-mysql-2@m.gmane.org

Re: Speeding up inserts in InnoDB

--0016367b689ed01ae70484d530d1
Content-Type: text/plain; charset=ISO-8859-1

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@cox.net> 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=johnny@pixelated.net
>
>


--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016367b689ed01ae70484d530d1--

Re: Speeding up inserts in InnoDB

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@cox.net> 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@gmail.com
>>
>>
>>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org