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
