which one is faster: “insert on duplicate update” OR “update with case”ON DUPLICATE KEY UPDATE faster...
Why is ra lower than re while la is higher than le?
How do I know my password or backup information is not being shared when creating a new wallet?
Current measurement op-amp calculation
What is formjacking?
How to know if I am a 'Real Developer'
How to encourage team to refactor
What does “to the numbers” mean in landing clearance?
Face Value of SOFR futures
Microphone on Mars
What happens if you declare more than $10,000 at the US border?
Does changing "sa" password require a SQL restart (in mixed mode)?
Why does finding small effects in large studies indicate publication bias?
How to read the error when writing vector files in QGIS 3.0
How many copper coins fit inside a cubic foot?
Is it possible to detect 100% of SQLi with a simple regex?
Identical projects by students at two different colleges: still plagiarism?
Is layered encryption more secure than long passwords?
Stream.findFirst different than Optional.of?
How can I make my enemies feel real and make combat more engaging?
How do I handle a blinded enemy which wants to attack someone it's sure is there?
How can changes in personality/values of a person who turned into a vampire be explained?
STM32 PWM problem
Why is Shelob considered evil?
put country dropdown in phtml file in magento2
which one is faster: “insert on duplicate update” OR “update with case”
ON DUPLICATE KEY UPDATE faster than UPDATEMySQL - best practices/ how to speed up "update, else insert' queries? Insert + delete faster?SELECT and UPDATE in a single query while updating selective fields of duplicate records onlyINSERT… ON DUPLICATE KEY UPDATE not working as I expectUPDATE and CASE problemUPDATE Column values with Case and JOINUpdate statement with strange side effects to dataUPDATE vs INSERT INTO… ON DUPLICATE KEY UPDATEBulk Updates (inserts, update) and check constraintsWhich is faster to update thousands of table rows?
Table:
CREATE TABLE t5 (
col1 VARCHAR(36) NOT NULL PRIMARY KEY,
col2 int(11) DEFAULT NULL,
col3 int(11) DEFAULT NULL,
UNIQUE (col2)
);
Records already inside:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);
Which of these two queries are expected to perform better:
Query-1:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
on duplicate key update
col2=if(values(col2) IS NULL, col2, values(col2)),
col3=if(values(col3) IS NULL, col3, values(col3));
Query-2:
update t5 set
col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END),
col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');
Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.
Condition of the table and the frequency the above query is performed as below:
- number of records is in hundreds of thousand
- query is performed once every x-seconds, each with ~150 keys
The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)
update mysql-8.0 bulk-update
edited 1 hour ago
Learning_DBAdmin
11410
asked 4 hours ago
nyoto arifnyoto arif
84
add a comment |
Table:
CREATE TABLE t5 (
col1 VARCHAR(36) NOT NULL PRIMARY KEY,
col2 int(11) DEFAULT NULL,
col3 int(11) DEFAULT NULL,
UNIQUE (col2)
);
Records already inside:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);
Which of these two queries are expected to perform better:
Query-1:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
on duplicate key update
col2=if(values(col2) IS NULL, col2, values(col2)),
col3=if(values(col3) IS NULL, col3, values(col3));
Query-2:
update t5 set
col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END),
col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');
Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.
Condition of the table and the frequency the above query is performed as below:
- number of records is in hundreds of thousand
- query is performed once every x-seconds, each with ~150 keys
The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)
update mysql-8.0 bulk-update
edited 1 hour ago
Learning_DBAdmin
11410
asked 4 hours ago
nyoto arifnyoto arif
84
add a comment |
Table:
CREATE TABLE t5 (
col1 VARCHAR(36) NOT NULL PRIMARY KEY,
col2 int(11) DEFAULT NULL,
col3 int(11) DEFAULT NULL,
UNIQUE (col2)
);
Records already inside:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);
Which of these two queries are expected to perform better:
Query-1:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
on duplicate key update
col2=if(values(col2) IS NULL, col2, values(col2)),
col3=if(values(col3) IS NULL, col3, values(col3));
Query-2:
update t5 set
col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END),
col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');
Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.
Condition of the table and the frequency the above query is performed as below:
- number of records is in hundreds of thousand
- query is performed once every x-seconds, each with ~150 keys
The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)
update mysql-8.0 bulk-update
edited 1 hour ago
Learning_DBAdmin
11410
asked 4 hours ago
nyoto arifnyoto arif
84
Table:
CREATE TABLE t5 (
col1 VARCHAR(36) NOT NULL PRIMARY KEY,
col2 int(11) DEFAULT NULL,
col3 int(11) DEFAULT NULL,
UNIQUE (col2)
);
Records already inside:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 1, 1),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', 2, 2);
Which of these two queries are expected to perform better:
Query-1:
insert into t5(col1, col2, col3) values
('872c18ba-35ae-11e9-91a5-88d7f65253d0', 2, 888),
('823c3d3a-35ae-11e9-91a5-88d7f65253d0', NULL, 999),
on duplicate key update
col2=if(values(col2) IS NULL, col2, values(col2)),
col3=if(values(col3) IS NULL, col3, values(col3));
Query-2:
update t5 set
col2=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 2 END),
col3=(case col1 when '872c18ba-35ae-11e9-91a5-88d7f65253d0' then 888 when '823c3d3a-35ae-11e9-91a5-88d7f65253d0' then 999 END)
where col1 in ('872c18ba-35ae-11e9-91a5-88d7f65253d0', '823c3d3a-35ae-11e9-91a5-88d7f65253d0');
Please take note in query-2, the statement is tailored from client side by pruning the NULL value first.
Condition of the table and the frequency the above query is performed as below:
- number of records is in hundreds of thousand
- query is performed once every x-seconds, each with ~150 keys
The objective is to reduce the I/O by not hammering the db with one single query on loop, as well as preventing a deadlock due to query taken too long time (yes, there was some deadlock earlier)
update mysql-8.0 bulk-update
update mysql-8.0 bulk-update
edited 1 hour ago
Learning_DBAdmin
11410
asked 4 hours ago
nyoto arifnyoto arif
84
edited 1 hour ago
Learning_DBAdmin
11410
asked 4 hours ago
nyoto arifnyoto arif
84
edited 1 hour ago
Learning_DBAdmin
11410
edited 1 hour ago
Learning_DBAdmin
11410
edited 1 hour ago
Learning_DBAdmin
11410
11410
asked 4 hours ago
nyoto arifnyoto arif
84
asked 4 hours ago
nyoto arifnyoto arif
84
asked 4 hours ago
nyoto arifnyoto arif
84
84
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
col2=if(values(col2) IS NULL, col2, values(col2))is equal tocol2=COALESCE(values(col2), col2).If ODKU causes
UNIQUE (col2)violation, the whole query will fail (see fiddle, first query).The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).
fiddle
Finally:
which faster: “insert on duplicate update” OR “update with case”
If no errors in queries and data INSERT .. ODKU is faster.
answered 3 hours ago
AkinaAkina
4,0461311
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230353%2fwhich-one-is-faster-insert-on-duplicate-update-or-update-with-case%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
col2=if(values(col2) IS NULL, col2, values(col2))is equal tocol2=COALESCE(values(col2), col2).If ODKU causes
UNIQUE (col2)violation, the whole query will fail (see fiddle, first query).The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).
fiddle
Finally:
which faster: “insert on duplicate update” OR “update with case”
If no errors in queries and data INSERT .. ODKU is faster.
answered 3 hours ago
AkinaAkina
4,0461311
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
add a comment |
col2=if(values(col2) IS NULL, col2, values(col2))is equal tocol2=COALESCE(values(col2), col2).If ODKU causes
UNIQUE (col2)violation, the whole query will fail (see fiddle, first query).The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).
fiddle
Finally:
which faster: “insert on duplicate update” OR “update with case”
If no errors in queries and data INSERT .. ODKU is faster.
answered 3 hours ago
AkinaAkina
4,0461311
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
add a comment |
col2=if(values(col2) IS NULL, col2, values(col2))is equal tocol2=COALESCE(values(col2), col2).If ODKU causes
UNIQUE (col2)violation, the whole query will fail (see fiddle, first query).The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).
fiddle
Finally:
which faster: “insert on duplicate update” OR “update with case”
If no errors in queries and data INSERT .. ODKU is faster.
answered 3 hours ago
AkinaAkina
4,0461311
col2=if(values(col2) IS NULL, col2, values(col2))is equal tocol2=COALESCE(values(col2), col2).If ODKU causes
UNIQUE (col2)violation, the whole query will fail (see fiddle, first query).The queries are not equivalent (second query will set col2=NULL for 1st record, see fiddle).
fiddle
Finally:
which faster: “insert on duplicate update” OR “update with case”
If no errors in queries and data INSERT .. ODKU is faster.
answered 3 hours ago
AkinaAkina
4,0461311
edited 3 hours ago
answered 3 hours ago
AkinaAkina
4,0461311
answered 3 hours ago
AkinaAkina
4,0461311
answered 3 hours ago
AkinaAkina
4,0461311
4,0461311
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
add a comment |
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
3. tested the query and it wont make col2=NULL. it will however update the value with the existing record (kind of update but no change)
– nyoto arif
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
@nyotoarif 3. tested the query and it wont make col2=NULL See fiddle. Your structure, your data, your queries (minor errors edited). col2 actually is set to NULL.
– Akina
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
sorry you were right! tested it again and it updated into NULL. can you please help with giving more detail info why ODKU will perform better?
– nyoto arif
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
@nyotoarif In practice - when stongly 2 strongly existing records strongly updating, no chance to detect the difference in execution time. Ever when you increase the number of updated records the difference will not become detectable I think. But the complexicity of a query will increase.
– Akina
3 hours ago
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230353%2fwhich-one-is-faster-insert-on-duplicate-update-or-update-with-case%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
