MySQL: Is it faster to use inserts and updates instead of insert on duplicate key update?

Question

I have a cron job that updates a large number of rows in a database. Some of the rows are new and therefore inserted and some are updates of existing ones and therefore update.

I use insert on duplicate key update for the whole data and get it done in one call.

But- I actually know which rows are new and which are updated so I can also do inserts and updates seperately.

Will seperating the inserts and updates have advantage in terms of performance? What are the mechanics behind this ?

Thanks!

Answer 1

In my test using ON DUPLICATE KEY UPDATE is in average 1.3 x slower than using Insert/Update. This is my test:

INSERT/UPDATE (54.07 sec)

    <?php 
       $mtime = microtime(); 
       $mtime = explode(" ",$mtime); 
       $mtime = $mtime[1] + $mtime[0]; 
       $starttime = $mtime; 
    ?> 
    <?php
    set_time_limit(0);
    $con = mysql_connect('localhost', 'root', '');
    mysql_select_db('test');

    for ($i = 1; $i <= 1000; $i = $i + 2)
    {
        mysql_query("
                    INSERT INTO users
                    VALUES(NULL, 'username{$i}', 'email.{$i}', 'password{$i}')
                    ");
    }

    for ($i = 1; $i <= 1000; $i++)
    {
        if ($i % 2 == 0)
        {
            mysql_query("
                    INSERT INTO users
                    VALUES(NULL, 'username{$i}', 'email.{$i}', 'password{$i}')
                    ");
        }
        else
        {
             mysql_query("
                        UPDATE users
                        SET (username = 'username{$i}', email = 'email{$i}', password = 'password{$i}')
                        ");
        }
    }
    ?>
    <?php 
       $mtime = microtime(); 
       $mtime = explode(" ",$mtime); 
       $mtime = $mtime[1] + $mtime[0]; 
       $endtime = $mtime; 
       $totaltime = ($endtime - $starttime); 
       echo "This page was created in ".$totaltime." seconds"; 
    ?>

ON DUPLICATE KEY UPDATE (70.4 sec)

<?php 
   $mtime = microtime(); 
   $mtime = explode(" ",$mtime); 
   $mtime = $mtime[1] + $mtime[0]; 
   $starttime = $mtime; 
?> 
<?php
set_time_limit(0);
$con = mysql_connect('localhost', 'root', '');
mysql_select_db('test');

for ($i = 1; $i <= 1000; $i = $i + 2)
{
    mysql_query("
                INSERT INTO users
                VALUES(NULL, 'username{$i}', 'email.{$i}', 'password{$i}')
                ");
}

for ($i = 1; $i <= 1000; $i++)
{
    mysql_query("
                INSERT INTO users
                VALUES({$i}, 'username{$i}', 'email.{$i}', 'password{$i}')
                ON DUPLICATE KEY UPDATE
                username = 'username{$i}', email = 'email{$i}', password = 'password{$i}'
                ");    
}
?>
<?php 
   $mtime = microtime(); 
   $mtime = explode(" ",$mtime); 
   $mtime = $mtime[1] + $mtime[0]; 
   $endtime = $mtime; 
   $totaltime = ($endtime - $starttime); 
   echo "This page was created in ".$totaltime." seconds"; 
?>