Memory Hierachy

Get this from http://dank.qemfd.net/dankwiki/images/d/dc/Memoryhierarchy.png

MySQL INSERT ON DUPLICATE UPDATE IS FASTER THAN UPDATE!!!

It is very strange. But the test result show it is.

MySQL

innodb_version                  5.6.13

protocol_version                10

version                               5.6.13-enterprise-commercial-advanced

version_compile_machine x86_64

version_compile_os           osx10.7

Result

SELECT udf_CreateCounterID(0,CURRENT_DATE);

SELECT @update,@updateend,@updatediff,@insertupdate,@insertupdate_end,@insertupdatediff,@keyval,@countlmt;

@update=2013-09-12 17:32:27
@updateend=2013-09-12 17:33:01
@updatediff=34

@insertupdate=2013-09-12 17:32:00
@insertdate_end=2013-09-12 17:32:27
@insertupdatediff=27

@keyval=13
@countlmt=1000000

Table

CREATE TABLE `sys_CounterID` (
  `exch_year` int(11) NOT NULL,
  `nextID` int(11) NOT NULL,
  PRIMARY KEY (`exch_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Test Function

CREATE DEFINER=`root`@`localhost` FUNCTION `udf_CreateCounterID`(exchID SMALLINT, listyear DATE) RETURNS int(10) unsigned
BEGIN
 /**
 counter ID is 32 bits, 
 highest 9 bits: exchange ID (until 2013,  totally 317 operator MIC. for any >511, modular 512)
 middel 7 bits: 2 digits year (max:99)
 left bits: counter number
 */
 DECLARE keyvalue INT UNSIGNED DEFAULT 0;
 
 SET @countlmt = 1000000;
 SET keyvalue = ((exchID % 512) << 9 ) + EXTRACT(YEAR FROM listyear) % 100;

 SET @keyval = keyvalue;
 SET @retVal =  0;

 SET @count = @countlmt;
 SET @insertupdate = SYSDATE();

 WHILE @count > 0 DO

  INSERT INTO `sys_CounterID`(`exch_year`,nextID)
  VALUE( keyvalue, 1)
  ON DUPLICATE KEY UPDATE 
   nextID = (@retVal := nextID + 1);

  SET @count = @count - 1;

 END WHILE;

 SET @insertupdate_end = SYSDATE();
 SET @insertupdatediff = TIMESTAMPDIFF(SECOND, @insertupdate,@insertupdate_end);

 
 SET @count = @countlmt;
 SET @update = SYSDATE();
 
 WHILE @count > 0 DO

  UPDATE sys_CounterID 
  SET nextID = (@retVal := nextID + 1)
  WHERE exch_year = keyvalue;

  SET @count = @count - 1;

 END WHILE;

 SET @updateend = SYSDATE();
 SET @updatediff = TIMESTAMPDIFF(SECOND, @update,@updateend);


 RETURN @retVal;

END