MySQL Forums
Forum List  »  MyISAM

Re: table lock for deleting and inserting
Posted by: Sreedevi Iyer
Date: October 23, 2009 11:19AM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: table lock for deleting and inserting
2675
October 23, 2009 11:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.