Analysis: INSERT ON DUPLICATE UPDATE VS UPDATE

[Walter Trapa]

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