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.
Best Answer
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 aWHERE
which contains aIN
with a large amount of static data, as well as the amount of connected reads and writes, intermediate caching, associated with that.The statement
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 theIN
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.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:
We also used
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 yourIN()
list ONE by ONE.The statement like
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:
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...