0

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)

0
  1. col2=if(values(col2) IS NULL, col2, values(col2)) is equal to col2=COALESCE(values(col2), col2).

  2. If ODKU causes UNIQUE (col2) violation, the whole query will fail (see fiddle, first query).

  3. 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.

  • 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 Feb 21 at 9:01
  • @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 Feb 21 at 9:03
  • 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 Feb 21 at 9:06
  • @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 Feb 21 at 9:32

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.