MySQL - Using INSERT ... ON DUPLICATE KEY for efficient queries

If you are using SELECT statement to retrieve a single row just so you can determine whether or not you need to apply an INSERT or UPDATE to it, then INSERT ... ON DUPLICATE KEY can help minimize queries and speed up execution time.

You will need MySQL 5.0+ to run this query.

Usage Example:

Let`s assume we have a table `logs` in some database.

logs
------
id - primary key, auto increment
username - unique index
lastactivity - datetime

Let`s say we have a table called "logs" in our database and we want to insert a record into "logs" if the username does NOT already exist in it, if the username already does exist, then we just want to update the `lastactivity` column.

Using PHP, here is an example of a poor way to solve this problem.

Code


$username = `testuser`;

$query = "SELECT id FROM logs where username = `$username`";
$go = mysql_query($query);
$rows = mysql_num_rows($go);
$id = mysql_result($go,0,`id`);
$currentDate = date("Y-m-d h:i:s");

if($rows == 1) //if that username already exists, perform an UPDATE
{
$query = "UPDATE logs SET lastactivity=`$currentDate` WHERE id = $id";
$go = mysql_query($query);
$rows = mysql_num_rows($go);
}
elseif($rows == 0) //username doesn`t exist, perform an INSERT
{
$query = "INSERT INTO logs(username,activity) VALUES(`$username`,`$currentDate`)";
$go = mysql_query($query);
$rows = mysql_num_rows($go);
}

The example above may work fine for awhile, but let`s assume that the $username variable is inside of a large for-loop that cycles through 100s or 1000s of usernames in a system. The example above will require 1 SELECT query, PHP logic, then an additional query (INSERT OR UPDATE), per iteration of the for-loop. From experience, once you are dealing with records in the hundreds, you will see extended loading times up to about 30-40 seconds per 900 records you cycle through using this method or a similar one.

That said, using INSERT ... ON DUPLICATE KEY can help speed up execution time drastically and reduce additional logic.

Here is a good example on how to accomplish the same thing above in PHP using INSERT ... ON DUPLICATE KEY.


$username = "testuser";
$currentDate = date("Y-m-d h:i:s");

$query = "INSERT INTO logs(username,activity) VALUES(`$username`,`$currentDate`) ON DUPLICATE KEY UPDATE activity=`$currentDate`";
$go = mysql_query($query);


Explained: Since the `username` column is a unique index, only 1 instance of `testuser` can be in the `logs` table. That said, the INSERT will only execute if `testuser` does not exist in the `logs` table, if `testuser` does exist, then the UPDATE query will execute and update that single row.

So if you were to take the code above and run it. Assuming the username `testuser` wasn`t already in your database, the query above would execute an INSERT statement. If you were to refresh the page, you would notice that the activity column would be updated with the $currentDate, which tells us that the UPDATE was executed since `testuser` was already in the logs table.

In this example, we accomplished the same thing as our first example but with only 4 lines of code and only 1 query. The speed increase is drastic, which results in lower CPU and MEMORY usage on your server, and overall increased responsiveness. In a real world test, a page loaded in under a second with roughly 900+ records in the database, a huge improvement over the 30-40 seconds it took prior with the code similar to the first example.

Keywords: MYSQL INSERT ON DUPLICATE KEY UPDATE


Submit a Comment