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?