Hi,
The perl code which does the update is below.
...
$dbh->do('LOCK TABLE BaseMessage WRITE');
$query = "UPDATE BaseMessage SET statusId = 3 WHERE baseMessageId= ?";
$updateSth = $dbh->prepare($query) or die "Prepare failed. ERROR: $DBI::errstr\n";
# Update the staus of the records to 'Processed'
foreach $messageId (@processedMessages) {
$updateSth->execute($messageId) or die "Unable to execute query: $query. ERROR: $DBI::errstr\n";
}
$updateSth->finish();
$dbh->do('UNLOCK TABLES');
...
As you can see the "LOCK" goes on before the loop which runs several update statements.
* SHOW CREATE TABLE tbl\G -- engine, indexes
CREATE TABLE `BaseMessage` (
`baseMessageId` int(11) unsigned NOT NULL AUTO_INCREMENT,
`fAdd` varchar(255) NOT NULL,
`tAdd` varchar(255) NOT NULL,
`xBG` varchar(255) DEFAULT NULL,
`envSnd` varchar(255) NOT NULL,
`rAdd` varchar(255) NOT NULL,
`eId` int(11) NOT NULL,
`bcId` int(11) NOT NULL,
`bcaEId` bigint(20) unsigned NOT NULL,
`sbsId` bigint(20) unsigned NOT NULL,
`aId` int(11) NOT NULL,
`tmpltId` int(11) NOT NULL,
`rcptDmn` varchar(255) NOT NULL,
`bcTypeId` int(1) NOT NULL,
`statusId` int(4) DEFAULT NULL,
`fName` varchar(255) NOT NULL,
`eceMssageId` varchar(255) NOT NULL,
`xBinding` varchar(255) DEFAULT NULL,
PRIMARY KEY (`baseMessageId`)
) ENGINE=MyISAM AUTO_INCREMENT=15822526 DEFAULT CHARSET=latin1
* SHOW TABLE STATUS LIKE 'tbl'\G -- sizes
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| BaseMessage | MyISAM | 10 | Dynamic | 13812671 | 217 | 3005761480 | 281474976710655 | 141750272 | 0 | 15822526 | 2010-03-18 16:11:06 | 2010-06-25 03:22:10 | NULL | latin1_swedish_ci | NULL | | |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
* SHOW VARIABLES LIKE '%buffer%'; -- cache size
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_size | 8388608 |
| innodb_log_buffer_size | 1048576 |
| join_buffer_size | 131072 |
| key_buffer_size | 1073741824 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 524288 |
| sql_buffer_result | OFF |
+-------------------------+------------+
The update works by finding the 'BaseMessageId' and updating it's value. I would've though that the primary key index is the best index for this kind of update. Is there a more adequate index I could be using?