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 <4rfv...@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=naclos...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Speeding up inserts in InnoDB
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 <4rfv...@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=joh...@pixelated.net
>
>
--
-
Johnny Withers
601.209.4985
joh...@pixelated.net
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=arch...@jab.org
