MySQL Forums
Forum List  »  Performance

Analysis: INSERT ON DUPLICATE UPDATE VS UPDATE
Posted by: Walter Trapa
Date: May 11, 2021 02:51AM

Hi,

I am looking for the explanation why this might happen.
We have a table which 200K records where 90% of the actions are for updates and the reaming 10% is for insert.

The development team used an "INSERTON DUPLICATATE UPDATE". but I felt something could be done there. So I simulated, with the exact same data (index, structure) a simple UPDATE.
(The backed should manage those 10% updates failing because no records and do the insert afterwards).

The results:

Connections: 1 5 10
InnoDB (5.6.51)
INSERT + DUPLICATE UPDATE 580 520 475
UPDATE 630 9% 600 15% 590 24%
NDB (5.6.28-ndb-7.4.10-cluster-gpl)
INSERT + DUPLICATE UPDATE 200 215 115
UPDATE 350 75% 380 77% 390 239%

Python Script used.


The objective is not comparing NDB vs InnoDB BUT the huge performance impact in NDB between these two statements? Why? I thought the first part of the INSERT + ON DUPLICATE UPDATE will first the position to do the update, but it seems it is not....


Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
Analysis: INSERT ON DUPLICATE UPDATE VS UPDATE
313
May 11, 2021 02:51AM


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.