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.

  • After the first query, the id's and referenced blocks are in the cache and therefore the access is faster. Retry after a while or with another set of records. I expect the second case to be a bit slower. – Ohadi Aug 13 '14 at 12:35
  • @Ohadi: That does not seem to be the case considering that this is a DML query. Additionally, the first UPDATE query never finishes. – dotancohen Aug 13 '14 at 12:42

I know it is not easy getting an execution plan for an update from MySQL since it only provides those on SELECT statements. But the clue might be in the order in which the records are updated, the evaluation of a WHERE which contains a IN with a large amount of static data, as well as the amount of connected reads and writes, intermediate caching, associated with that.

The statement

UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN (1,...,10000); 

is a type of statement we try to avoid when updating larger databases since the parser seems to go wild on them from time to time. IN ( a,b,c,...,ZZZZ ) for me has become a coding style only suitable for very small item numbers in the IN data. I am working on a open source project where I frequently run into what I call "remote minded join", the second half usually looks exactly like your problem.

SELECT id FROM all_our_customers WHERE happytospam=1 AND LENGTH(email) > 6;
Storing result on client side as string like 
LOOP over results
$all_ids += ",$next_result";
$all_ids = SUBSTRING($all_ids,1); 
ending up with a string like 
"1,2,3,4,5,8,10,100,1000,...,100000" in $all_ids
UPDATE humans_we_respect SET expressed_interest=1 WHERE id IN ( $all_ids )

While the first part usually executes lightning fast, the second part takes forever, which is what you describe as well. These queries can usually be sped up EXTREMELY by rewriting them as:

UPDATE humans_we_respect,all_our_customers 
SET humans_we_respect.expressed_interest=1 
WHERE all_our_customers.id = humans_we_respect.id 
AND all_our_customers.happytospam=1 
AND LENGTH(all_our_customers.email) > 6

We also used

UPDATE humans_we_respect 
SET expressed_interest=1 
WHERE id IN ( 
FROM all_our_customers 
WHERE happytospam=1 
AND LENGTH(email) > 6 

which performed better than the original but not as good as my suggested version.

This all is assuming that you use proper indexes with primary on ID and combined multi column indexes where multiple columns are frequently used together or have a good significance together and are usually present in your queries.

The Clue is that large amounts of static values in IN clauses increase the execution time nearly exponentially on queries with many matching records, since they basically do NOT USE ANY INDEXES OR OPTIMIZATIONS and usually end up in full table scans, in which IMHO the execution will examine every record/line in your table by comparing it with every item in your IN() list ONE by ONE.

The statement like

INSERT INTO humans_we_respect (id) VALUES (1),...,(10000) ON DUPLICATE KEY UPDATE expressed_interest=1;

however is using the index to locate the record and then updates it, even so not intended for this use, it will run much better due to the use of a index on ID if there is one and will only do a index-lookup for the record rather than thousands of comparisons! Working directly with two tables might however be faster if the list of IDs is derived from another table on the same server, there are more optimizations that can be used plus you do not have to transfer data out-of and into the mysql server process.

Just as some extra info:

UPDATE humans_we_respect SET expressed_interest=1 WHERE id='1' OR id='2' OR ...

is a good technique to optimize IN() queries with very low counts of elements since it will create a parallel index query for every element, which is great for the first elements but will decrease in performance with more elements a lot, and at some point hit the limit of the parser for optimization (IMHO it might be 255 elements in one query) at which point it will grind to a snails pace again...


Just a guess:

  • Maybe mysql checks in the first query mysql checks 17 million rows, if one fit in the IN clause.
  • in the second query you insert only 10000 rows, check the key by index and update the rows.

If this is the case you could try to rewritee the first query to UPDATE... WHERE id <= 10000, but this only works if you realy have to update 10000 rows with consecutive ids. Maybe you can also try UPDATE ... WHERE id = 1 OR id = 2 OR .... But this would only be fast, if mysql can optimize this internally.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.