I have a table with about 17 million rows:
mysql> describe humans_we_respect;
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| name | varchar(63) | YES | | NULL | |
| address | varchar(127) | YES | | NULL | |
| city | varchar(63) | YES | | NULL | |
| state | varchar(3) | YES | MUL | NULL | |
| zip | varchar(15) | YES | | NULL | |
| country | varchar(15) | YES | | NULL | |
| email | varchar(127) | YES | | NULL | |
| website | varchar(127) | YES | | NULL | |
| area_code_state | varchar(3) | YES | MUL | NULL | |
| timezone | set('other','pacific','mountain','central','eastern','alaska','hawaii') | YES | | other | |
+---------------------+-------------------------------------------------------------------------+------+-----+---------+-------+
12 rows in set (0.01 sec)
Due to the strict nature of only contacting those who expressed interest in a newsletter, and the strict nature of never contacting someone who asked not to be contacted, before a mailing I added a field expressed_interest (tinyint) deafult null
which I switch to 1
for those who expressed interest, and then switch to null
for those who asked not to be contacted.
The following query, in which 10000 rows are updated per query, takes a very long time to run (killed after half an hour):
UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000);
However the following query completes in seconds:
INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;
Under what conditions will ON DUPLICATE KEY UPDATE
be faster than UPDATE
? I would like to know this for future use with large tables such as this.
This is on MySQL 5.5.33 running in Amazon RDS.
UPDATE
query never finishes.