MySQL Forums
Forum List  »  Optimizer & Parser

Re: bulk update very slow
Posted by: james che
Date: September 26, 2012 02:58PM

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 |

Options: ReplyQuote


Subject
Views
Written By
Posted
4746
September 25, 2012 02:51PM
Re: bulk update very slow
1823
September 26, 2012 02:58PM
2888
September 27, 2012 12:01PM
1866
September 27, 2012 08:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.