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);

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.

Google DNS vs OpenDNS vs Verizon DNS

The number one complaint people have with computers, no matter what operating system, is speed. And part of the problem with the internet is all the outside variables that affect your system speed.

One of the ways you can increase your browsing speed is to change your DNS settings. DNS is what translates timlinden.com into an IP address so your browser can load the site. If your DNS is slow everything will be slow.

The problem is that DNS depends on the website you are going to. If your internet provider has a lot of customers going to facebook.com for example, they’ll already know the IP address to send to you. So it loads fast.

Well I just found a neat tool that lets you compare DNS providers based on your history. That’s right, they see how long it would take the providers to return the DNS for sites you actually go to.

What I learned is COX (the cable company around here I’d never go back to) is 6.4% slower than my own ISP Verizon. But interestingly enough OpenDNS (which I thought was faster) is also slower at -3.4%. Google Public DNS came in at a slight 1.6% faster speed.

So if I switch to Google’s DNS (I’m using OpenDNS right now) in theory websites should load about 4% faster. I’m going to try it and give it a whirl.. Maybe it’ll make arriving at sites faster so I can win the next tournament.. hahahahaaa

Trying out CloudFlare

I’ve been trying out a service called CloudFlare on my blog and ClickTrackProfit. The goal of CF is to add a layer of security to your website.

It blocks bots, spammers, and viruses from even getting to your server. So it’s like a firewall. But at the same time it caches your static content like images and javascripts. So it’s like a CDN.

To my surprise it’s worked very good and was incredibly easy to setup. All you do is transfer your DNS to them, and they take care of the rest. It’s like magic. It’s been working so great and easy that I thought it wasn’t doing anything!

But it is, and it’s reduced the load by about 50%. A small fraction is the bots being blocked, but the majority is the system caching content making it faster and reducing our bandwidth bill. Cool!

Without going into details, I haven’t tried it with any of our traffic exchanges. We have a unique situation where at the present time CloudFlare wouldn’t work with our setup, but I am working on it so we can. The benefits so far have been worth it!

Found the Select All Friends on Facebook Solution

Looking for a fun game to Invite all your Friends on Facebook to? Check out Sitizens! See if you can become King of Facebook.com!

Previously I wrote a post and a video about how to select all your friends on facebook. It stopped working when they changed some code. Well after much tinkering, I found the solution and updated the post. The key is to open the select your friends page in a new tab.

They now have it in an iframe, whereas before it wasn’t. Simple but didn’t have the time to figure it out. Just remember, only invite your friends to things they’ll actually support. And if you use the above trick please tweet, like on facebook, and blog about it cuz it’s helping lots of people ;-)

Oh and stay subscribed if you want to find out how to do it later if they change it lol

How To Select All Friends in Facebook [v2]

Looking for a fun game to Invite all your Friends on Facebook to? Check out Sitizens! See if you can become King of Facebook.com!

When you are trying to invite your friends to your page, it can be tedious. So here’s a quick javascript to select all your friends in Facebook so you can invite them all in one quick motion.

Simply copy and paste that into the address bar when you have the friend selector window open. I have a video to show you exactly what I mean here:

So now you know how to select all friends on Facebook for an event, the easy way. You can use it to invite friends to your facebook groups, pages, anywhere the friend picker window shows up!

As of 3-29-2011 this method works. If you can’t get it to work on your end, try again. I use this method myself so if it stops working I’ll update the code.

Can’t get it to work? Try right clicking in different places in the select friends window. I found right clicking on a photo worked for me. Also note that it can take a long time for your browser to select them all, and after submitting it appears to do nothing when it actually is.

4-13-2011 This method is not currently working. However a Firefox Plugin does this very well. I will post an update when I’ve got a new code figured out ;-)

I’m using CloudFront, and it’s great!

Amazon has officially released the CloudFront Public Beta! What is CloudFront? It’s a Content Delivery Network (CDN). Essentially a CDN is a network of servers all around the world, so when you visit a site it grabs the content from the closest server to you.

So what does this mean exactly? Well I’ve been using CloudFront for about a month or so to host my video advertisements on the traffic exchanges. What this does is ensures my videos load quickly for viewers, which is crucial for traffic exchanges. If you’ve only got 10 seconds to view the ad, and it can’t load quick, well it’s pointless!

This past week I’ve also switched from CDNLayer (SoftLayer CDN) to Amazon’s CloudFront on much of the static content on StartXchange. And I’ll be moving over more content as time allows. I started noticing, and getting complaints, from surfers that CDNLayer was actually slowing surfing down for them. So far switching to CloudFront has been much better performance.

The main reason I’m excited about this is because it’s Amazon. They are truely innovators in this whole cloud computing thing, and they are doing it all with very good pricing. It is a bit more complicated having to get the files to Amazon, but once you figure that out it’s 100% off your server so you don’t have to worry about the disk space or resources or anything!