Re: table lock for deleting and inserting
Thanks rick for your reply.
Here is the insert statement in perl script A :-
$q_demo = "INSERT INTO schema.tablea (user, us_state, zip, country, partner_id)"
. " VALUES ($user, '$state_code', '$zip_code', '$country_code',$idPartner) "
. " ON DUPLICATE KEY UPDATE us_state = '$state_code', zip = '$zip_code', country = '$country_code'";
create table statement :
CREATE TABLE `tablea` (
`id` int(10) unsigned NOT NULL auto_increment,
`user` int(10) unsigned NOT NULL,
`us_state` varchar(2) default NULL,
`country` varchar(2) default NULL,
`zip` varchar(5) default NULL,
`last_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`partner_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user` (`user`,`partner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=43186443 DEFAULT CHARSET=latin1 |
Here is the delete statement in perl script b :- In this script, I'm reading a line from a file and deleting one row at a time from the table. It took 45 minutes to delete 300,000 rows, which is too much time.
DELETE from SCHEMA.TABLEa where user = $user; (This $user is from the file)