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:
{code type=php}$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);
}{/code}
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:
{code type=php}$query = “INSERT INTO `logins` SET `Date`=’09-17-2012′, `Logins`=1 ON DUPLICATE KEY UPDATE `Logins`=`Logins`+1”;
$result = mysql_query($query);{/code}
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.