Re: bulk update very slow

[james che]

james che Wrote:
-------------------------------------------------------
When check the query profiler, I find the init state take long time, what does it mean?

> 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 |