MySQL Forums
Forum List  »  InnoDB

Delete query very slow and locking other tables
Posted by: Dhananjay raju
Date: January 19, 2009 03:34AM

Hi iam executing one delete query for every 5 mins to remove sessions which stay in table for more than 24 hrs.
here is the table structure
CREATE TABLE `tbl_sessions` (
`session_id` char(32) NOT NULL,
`ip_addr` char(15) NOT NULL,
`expire_time` int(10) unsigned NOT NULL,
`test_data` text NOT NULL,
`ref_data` text NOT NULL,
PRIMARY KEY (`session_id`),
KEY `ip` (`ip_addr`),
KEY `idx_expire` (`expire_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
expire_time -> Unix time stamp
The problem is
1.There will be a frequent inserts into the table.
the insert query is
INSERT INTO `tbl_sessions` (session_id,ip_addr,expire_time,data) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE session_id=?, expire_time=?, test_data=?

2)The delete query will be like
DELETE FROM tbl_sessions WHERE expire_time<?(unix time stamp will the value here)

the delete query is very slow,bcz of that all the insert's are being locked until the delete executes.

How can i increase the performance of this delete query ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Delete query very slow and locking other tables
4706
January 19, 2009 03:34AM


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.