GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

update very slow

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • update very slow

    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?

  • #2
    What does the EXPLAIN for the SELECT look like?

    How many rows match if you remove the LIMIT 1100?

    Comment


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

      Comment


      • #4
        Add this and let me know if there's any difference:

        ALTER TABLE scheduled_messages ADD INDEX sm_ix_schedtime_status_batchid(scheduled_time, status, batchID);

        Comment


        • #5
          What I don't understand is why select is fast, but update take long er time with init state?

          Comment


          • #6
            It's using an index that gets updated. I think an index on (scheduled_time, status,aggregatorID,shortcodeID) will be faster.

            Comment


            • #7
              Should not the select and update use the same index?

              Comment

              Working...
              X