bulk update very slow
Posted by:
james che
Date: September 25, 2012 02:51PM
I have a update query, it runs very slow (take about 5 minutes), but if convert it to select query, it only take 30 seconds.
This one take 5 minutes
UPDATE scheduled_messages SET `batchID`='17' WHERE scheduled_time = '2012-09-12 15:00:00' AND status = 'scheduled' AND batchID IS NULL ORDER BY aggregatorID ASC,shortcodeID ASC LIMIT 1100
This one only take 30 seconds.
select * from scheduled_messages WHERE scheduled_time = '2012-09-12 15:00:00' AND status = 'scheduled' AND batchID IS NULL ORDER BY aggregatorID ASC,shortcodeID ASC LIMIT 1100
CREATE TABLE `scheduled_messages` (
`scheduled_message_id` bigint(20) NOT NULL AUTO_INCREMENT,
`batchID` int(11) DEFAULT NULL,
`subscriber_id` int(11) DEFAULT NULL,
`oneTime` tinyint(1) DEFAULT NULL,
`sendType` varchar(9) COLLATE utf8_unicode_ci DEFAULT NULL,
`message_type` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`message_text` tinytext COLLATE utf8_unicode_ci,
`scheduled_time` datetime DEFAULT NULL,
`status` tinytext COLLATE utf8_unicode_ci,
`aggregatorID` int(11) DEFAULT NULL,
`shortcodeID` int(11) DEFAULT NULL,
`retry_log_id` int(11) DEFAULT NULL,
PRIMARY KEY (`scheduled_message_id`),
KEY `idx_time` (`scheduled_time`),
KEY `idx_retry_log_id` (`retry_log_id`),
KEY `idx_subscriber_id` (`subscriber_id`),
KEY `batch` (`batchID`)
) ENGINE=InnoDB AUTO_INCREMENT=189331034 DEFAULT CHARSET=utf8 COLLATE=utf8_unico
de_ci |