Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts

Thursday, September 12, 2013

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