update very slow

MySQL & MariaDB Other MySQL® Questions
1

I have a update query, it runs very slow (take about 5 minutes), but if convert it to select query, it only take 10 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 10 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,
batchIDint(11) DEFAULT NULL, subscriber_idint(11) DEFAULT NULL, oneTimetinyint(1) DEFAULT NULL, sendTypevarchar(9) COLLATE utf8_unicode_ci DEFAULT NULL, message_typevarchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, message_texttinytext COLLATE utf8_unicode_ci, scheduled_timedatetime DEFAULT NULL, statustinytext COLLATE utf8_unicode_ci, aggregatorIDint(11) DEFAULT NULL, shortcodeIDint(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_unicode_ci

When check the query profiler, I find the init state take almost all the time, what does it mean?

2

What does the EXPLAIN for the SELECT look like?

How many rows match if you remove the LIMIT 1100?

3

The explain output:
id: 1
select_type: SIMPLE
table: scheduled_messages
type: index_merge
possible_keys: idx_time,batch
key: idx_time,batch
key_len: 9,5
ref: NULL
rows: 32857
Extra: Using intersect(idx_time,batch); Using where; Using filesort

It return about 2000 rows without limit.

Thanks