Delete query very slow and locking other tables
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 ?