Tim Linden

I'm the CTO of TimTech, I love coding with PHP and MySQL.
I'm addicted to tracking clicks and email marketing.

ON DUPLICATE KEY UPDATE

When you are working with high traffic sites it’s important to optimize every query you can. It’s also important to realize with so much traffic multiple people could be doing the same thing so data can messy.

Say you create a new table to track how many users login to your site every day. You create a table with the fields Date and Table. I used to do it this way:

Select Code
1
2
3
4
5
6
7
8
9
$query = "SELECT * FROM `logins` WHERE `Date`='09-17-2012'";
$result = mysql_query($query);
if (mysql_num_rows($result) == 0) {
$query = "INSERT INTO `logins` SET `Date`='09-17-2012', `Logins`=1";
$result = mysql_query($query);
} else {
$query = "UPDATE `logins` SET `Logins`=`Logins`+1 WHERE `Date`='09-17-2012'";
$result = mysql_query($query);
}

The problem is two fold: There are two queries for one task, and if you have a thousand people hitting that page at the same time there can be multiple inserts into the logins table.

Yes, you can make sure MySQL doesn’t allow multiple inserts, but it’ll result in an error and those logins won’t be counted.

What I found was a new way to do the same thing.. In one query, like so:

Select Code
1
2
$query = "INSERT INTO `logins` SET `Date`='09-17-2012', `Logins`=1 ON DUPLICATE KEY UPDATE `Logins`=`Logins`+1";
$result = mysql_query($query);

That’s it.. ON DUPLICATE KEY UPDATE tells MySQL to update the table if the key is a duplicate. Here the table structure would have the Key being “Date” and set to unique.

 
1 Kudos
Don't
move!

3 comments for “ON DUPLICATE KEY UPDATE

  1. September 17, 2012 at 9:46 am

    Simple but very useful, thanks Tim

  2. September 17, 2012 at 10:12 am

    Why not just add ‘username’ to the table as part of the primary key along with date?

  3. September 17, 2012 at 2:54 pm

    Every little bit helps the server load, great tip, Tim. Like you, I was doing multiple queries. Doh! I will update my scripts, thanks.

Comments are closed.

%d bloggers like this: