Update: The where clauses are the same. The results are as outlined below.
The following query runs within 0.1s and returns 0 results.
SELECT id from content WHERE compliance_overridden = 1 AND compliance_overridden_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
Hence I would expect the following query to return within the same time (as it doesn't actually have to update anything).
UPDATE content SET compliance_overridden = 0 WHERE compliance_overridden = 1 AND compliance_overridden_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
It is taking a few minutes to complete.
a) Why?
Using EXPLAIN shows that the first query is not using any indexes (key=None) but the second query is (key=PRIMARY). That confuses me even more.
b) Why does one query use an index, but not the other? Maybe MySQL selects the rows first and then updates by primary key? I have tried to run the two queries separately (select ids, then update) and both are fast.
c) Why would the second query be slower when using the PRIMARY index over no index?
PS: I have tried adding USE INDEX (PRIMARY) to the first query and IGNORE INDEX (PRIMARY) to the second query, both have no effect on query times.
PPS: Using SQL_NO_CACHE for the first query has no effect on query times either.
Engine: Aurora MySQL 5.6.10a
SHOW CREATE TABLE content;
CREATE TABLE `content` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`content_key` char(40) NOT NULL,
`body` mediumtext,
`original_body` mediumtext,
`meta` mediumblob,
`status` enum('approved','deleted','draft','library','flagged','in progress','pending','published','publishing error','queued','ready','retracted','scheduled','removed','for review','reviewed','disabled') NOT NULL DEFAULT 'in progress',
`is_flagged` int(11) NOT NULL DEFAULT '0',
`extraction_status` enum('queued','processing','processed','processing error') DEFAULT NULL,
`type` enum('internal','external','library') NOT NULL DEFAULT 'internal',
`compliance_status` enum('ok','error','warning','unchecked','fail','overridden-ok','overridden-error','queued') DEFAULT 'unchecked' COMMENT 'Status when content is reviewed against guidelines',
`queued_for_compliance` tinyint(4) NOT NULL DEFAULT '0',
`queued_date` timestamp NULL DEFAULT NULL,
`review_retries` int(11) NOT NULL,
`review_notes` varchar(255) DEFAULT NULL,
`needs_review` tinyint(1) DEFAULT '0' COMMENT 'Indicates that the content items needs the compliance reviewed to be checked',
`pushed_for_review` enum('no','yes','error') NOT NULL DEFAULT 'no' COMMENT 'Flags if content is pushed to Minerva.',
`risk_score` tinyint(1) DEFAULT '0',
`keywords` text,
`track` varchar(60) DEFAULT NULL,
`purpose` enum('arbitrary','compliance_file_check','compliance_text_check','document','promotion','reply','retweet','site','site_post','soa','status_update','url') NOT NULL DEFAULT 'site' COMMENT 'Changed to an enum with applicable values. This will allow us to optimise queries on this column.',
`purpose_note` text COMMENT 'Optional note explaining the purpose of this item',
`format` varchar(20) DEFAULT NULL,
`media` varchar(255) DEFAULT NULL,
`media_alternate` varchar(255) DEFAULT NULL,
`mime_type` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`tag_id` int(11) DEFAULT NULL,
`email_template_id` int(11) DEFAULT NULL,
`social_account_id` int(11) DEFAULT NULL,
`target_social_account_id` int(11) DEFAULT NULL,
`parent_id` int(11) DEFAULT NULL,
`notes` text,
`options` text,
`compliance_notes` text,
`notified` tinyint(1) NOT NULL COMMENT 'Indicates if legal have been notified of the content change',
`publish_immediately` tinyint(1) DEFAULT '0',
`score` int(11) DEFAULT NULL,
`insights` text,
`publish_date` timestamp NULL DEFAULT NULL,
`date_published` timestamp NULL DEFAULT NULL,
`external_id` varchar(40) DEFAULT NULL,
`feed_id` int(11) DEFAULT NULL,
`site_id` int(11) DEFAULT NULL,
`client_id` int(11) DEFAULT NULL,
`has_changed` tinyint(1) DEFAULT '0' COMMENT 'Indicates if the content has ever changed',
`was_flagged` tinyint(4) NOT NULL DEFAULT '0',
`last_flagged` timestamp NULL DEFAULT NULL COMMENT 'Date content was last flagged',
`compliance_overridden` tinyint(4) NOT NULL DEFAULT '0',
`compliance_overridden_date` timestamp NULL DEFAULT NULL,
`was_risky` tinyint(1) DEFAULT '0' COMMENT 'Indicates if the content was ever risky',
`last_risky` timestamp NULL DEFAULT NULL COMMENT 'Date content was last assigned risk',
`reference_urls` enum('none','ok','unchecked','error') NOT NULL DEFAULT 'none',
`screenshot_url` text,
`rescan` enum('no','manual-queued','compliance-changed','compliance-unchanged','compliance-unchecked') NOT NULL DEFAULT 'no',
`rescan_rules` varchar(50) DEFAULT NULL,
`applicable_rules` text COMMENT 'Applicable rules for a review',
`lsh_hash` char(70) DEFAULT NULL,
`comment_count` int(11) NOT NULL DEFAULT '0',
`modified` timestamp NULL DEFAULT NULL,
`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `content_key` (`content_key`),
KEY `fk_content_user` (`user_id`),
KEY `fk_content_content` (`parent_id`),
KEY `fk_content_email_template` (`email_template_id`),
KEY `fk_content_target_social_account` (`social_account_id`),
KEY `fk_content_feed` (`feed_id`),
KEY `fk_content_site` (`site_id`),
KEY `title` (`title`),
KEY `status` (`status`),
KEY `type` (`type`),
KEY `needs_review` (`needs_review`),
KEY `compliance_status` (`compliance_status`),
KEY `risk_score` (`risk_score`),
KEY `purpose` (`purpose`),
KEY `extraction_status_idx` (`extraction_status`),
KEY `client_id` (`client_id`),
KEY `is_flagged_idx` (`is_flagged`),
KEY `purpose_idx` (`purpose`),
FULLTEXT KEY `content_index` (`title`,`body`),
CONSTRAINT `content_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `client` (`id`),
CONSTRAINT `fk_content_content` FOREIGN KEY (`parent_id`) REFERENCES `content` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_content_feed` FOREIGN KEY (`feed_id`) REFERENCES `feed` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_content_site` FOREIGN KEY (`site_id`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_content_social_account` FOREIGN KEY (`social_account_id`) REFERENCES `social_account` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `fk_content_target_social_account` FOREIGN KEY (`social_account_id`) REFERENCES `social_account` (`id`),
CONSTRAINT `fk_content_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=247236 DEFAULT CHARSET=utf8
Best Answer
In the update statement, MySQL has to compute "date_sub(created, interval 30 day)" for each row, and compare it with compliance_overridden_date. In the select, it only has to compute date_sub(now(), interval 30 day) once. Your where-clause does not allow MySQL to use an index to select the rows to be modified. And since all rows must be read, it's better to scan the table than skipping between index and data. I have no explanation for the "PRIMARY" in the explain. Your guess is better than mine.