# update very slow

**URL:** <https://forums.percona.com/t/update-very-slow/1903>\
**Category:** Other MySQL® Questions\
**Created:** [September 28, 2012, 3:55pm UTC](https://forums.percona.com/t/update-very-slow/1903 "2012-09-28T15:55:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![baoxiao](https://avatars.discourse-cdn.com/v4/letter/b/f6c823/32.png) [@baoxiao](https://forums.percona.com/u/baoxiao)\
**Post date:** [September 28, 2012, 3:55pm UTC](https://forums.percona.com/t/update-very-slow/1903/1 "2012-09-28T15:55:57Z")

</div>

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,  
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\_unicode\_ci

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

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [October 2, 2012, 8:48am UTC](https://forums.percona.com/t/update-very-slow/1903/2 "2012-10-02T08:48:56Z")

</div>

What does the EXPLAIN for the SELECT look like?

How many rows match if you remove the LIMIT 1100?

---

<div class="post-metadata">

**Author:** ![baoxiao](https://avatars.discourse-cdn.com/v4/letter/b/f6c823/32.png) [@baoxiao](https://forums.percona.com/u/baoxiao)\
**Post date:** [October 2, 2012, 10:39am UTC](https://forums.percona.com/t/update-very-slow/1903/3 "2012-10-02T10:39:13Z")

</div>

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