MySQL Forums
Forum List  »  Performance

Re: Analysis: INSERT ON DUPLICATE UPDATE VS UPDATE
Posted by: Walter Trapa
Date: May 12, 2021 01:24AM

Sorry, for some reason it was finally posted like that after I edited properly but I didn't know no formatting is allowed.

Innodb (5.6.51)
NDB (5.6.28-ndb-7.4.10-cluster-gpl) 2 data nodes


(I) Insert on Duplicates Update
(U) Update only


INSERT + DUPLICATE UPDATE
UPDATE 75% 77% 239%


NDB
Connections: 1
(I) 200
(U) 350
Inc: 75%
Connections: 5
(I) 215
(U) 380
Inc: 77%
Connections: 10
(I) 115
(U) 390
Inc: 239%


INNODB
Connections: 1
(I) 580
(U) 630
Inc: 9%
Connections: 5
(I) 520
(U) 600
Inc: 15%
Connections: 10
(I) 475
(U) 390
Inc: 24%



The simulation, update 10K records and repeat 10 times, so 100K records finally updated.


The update and the insert are not completely the same (In our case, the update will send less columns)
UPDATE sends: 6 columns + 3 WHERE
INSERT ON DUPLICATE UPDATE sends: 10 for the insert + 6 UPDATE + 3 WHERE

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Analysis: INSERT ON DUPLICATE UPDATE VS UPDATE
92
May 12, 2021 01:24AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.