Alex Schultz: APIs

mysql copy table

Continuing my chain of blatantly obvious mysql queries that I nonetheless didn't know at one stage :) copying a table isn't an obvious function in mySQL. So here's how to do a mysql copy table

Say you have a table called "tbl_theoriginal" which has the perfect, ideal, wonderful table structure for a new table you want "tbl_thenewone" and you would like to create it the query you would create is as follows:

CREATE tbl_thenewone SELECT * FROM tbl_the original

Simple huh! You can put in any WHERE clause you like and even a limit 1,1 or whatever if you don't want to transfer any content but just the table structure.

mySQL rename table

One of the (few) downsides of  phpMyAdmin (at least the version I use) is that it doesn't support the mySQL rename table function.

It is very simple to use this function through the SQL tab in phpMyAdmin (or any other direct interface with mysql). Say you want to change the name of the table from "tbl_oldtable" to "tbl_newtable" then this would be the code:

RENAME TABLE tbl_oldtable TO tbl_newtable

Simple huh! Right with that duty to my readers and hopefully my natural search traffic... (these mySQL posts tend to do pretty well) I am off to drink more cabernet sauvignon :)

New Site Launch Huge Success

I have been working on a new design for my cocktail recipes site for about 2 months now. The company I selected to do the design were 2 pixel solutions (based in India) who I found through elance. You can see the results of the redesign below.

I have been really pleased with working with the 2 pixel team. They were really good in making the changes I asked for and simplified the design extremely well. I would highly recommend anyone using elance to ask for an outline mock up of the design a company is thinking of creating ahead of time for your site. I certainly benefitted from that. 

Having launched the site yesterday I took a look at the very early results and am over the moon. All the visitor level metrics are looking great. Visitors are spending nearly 25% longer on the site and viewing 30-40% more pages on each visit. I also found a number of features that were broken as I recoded the site (and honestly broke some more myself) so I have some fixing to do but bottom line the new site ROCKS :) and my users (most importantly) seem to love it.

For the rest of today I am working on getting my facebook application up and running again.

A little bit of an update on the Stumble Testing

Using Stumbleupon buttons on my paper airplanes site was an interesting idea that I tried a few weeks back. It seems to be working pretty well. Below you see the graph of traffic I am getting from Stumble over time from the last big stumble spike (following which I added the stumble button).

For November (i.e. not including the spike in october) Stumbleupon now makes up 10% of my site growth and approx. 1% of all site traffic. I have a fair few ideas for how to boost this source of traffic even more and am really keen to give it a go soon!!!

Affiliates more Profitable than Malware

Sunbelt Blog is a really fascinating blog for anyone who works in the internet industry. As an affiliate I find it a really fascinating blog since the kind of people who produce malware and so on also are often affiliates. In the early days of running my own site I promoted a company who produced a software that when installed on someone's computer replaced ads on any site with their own ads. If someone came to my site, used my bandwidth, read my content they wouldn't see my adverts and wouldn't earn me a penny if they had installed any of this company's software. The company was called Gator and is now called Claria and have been through a number of legal cases on this which means what they do is legal although distasteful to me.

Anyway something that is very common in the malware industry is to get someone to install a piece of software that does something to your PC in order to get hold of a pornographic video or some other slightly illicit goody. They may hide the fact this download is making you part of a botnet but in the latest example shown on the sunbelt blog they get you to download google pack. The 2 incredible parts of this is that the Google affiliate program is paying more than malware pays and that what appears to be a relatively experienced naughty group would risk attacking an affiliate program. Usually these programs are on top of their affiliates enough that they would catch this, stop it and would simply not pay the affiliate. I wonder why Google appeared an easy target. Was the payout big enough to be interesting, the conversion to Google pack so great that it was worth doing or does Google not have a great affiliate fraud monitoring system in place yet?

Stumbleupon Traffic Source

As many people have noticed stumbleupon has become a pretty good source of organic traffic. For me stumbleupon is 10-20% of the growth of my paper airplanes site at the moment and I know I am by no means the most popular paper airplanes site on stumbleupon right now (btw that wireframe rocks... a future project for me too I think).

I may not be the top paper airplanes site in stumbleupon and it certainly isn't my biggest source of traffic but it is now a significant enough contributor with lots of potential that I want to spend some time on it. The main issue as many people have seen is that traffic from stumbleupon is very bumpy indeed:

What is interesting though is though each bump is adding up to hundreds of visitors (sometimes near 1000) they are interspersed with long periods of zero (or v. low) traffic. The last spike you can see was followed by some level of sustained traffic, not huge but a significant part of my daily unique visitor growth. The main reason for this growth seems to have been adding a stumbleupon button to every page of my site.

I guess I will see how this sustains but for now things are looking pretty good for a new sustained source of traffic to paperairplanes.co.uk. Why don't you give it a go too?

Squeezing out Efficiencies

The growth of my cocktail recipe website has been truly scary this year. I am into my second year of >100% year on year growth in the site, I have doubled in volume since January and if anything growth is accelerating.

 The plan of course is to keep growing and doing well. I am currently at 2/3 of the bandwidth limit my web hosting company allow me which although cool is very scary when you look at last year's traffic distribution numbers:

The above December to October ratio is typical, I have seen it for the past 7yrs every year with December having roughly 3x the traffic of October (you can see the huge growth in last year's graph from Jan to Dec as well). In the peak day of december (new year's eve) I expect to be serving about 4cocktails a second (30% above my current recorded intraday peak) with an intraday peak of 7 cocktails a second. My code/databases can stand this but I am not a professional coder and want to be sure so I am working on making my code more efficient at the moment, adding more caching, planning (finally) to migrate to a very professional hosting package and place all images/css on a content delivery network like Amazon S3 (because whatever happens my current bandwidth is screwed).

The main point of this note is to encourage you guys to check out your code. I just recoded the displaycocktail.php script on my site (which accounts for 20% of bandwidth and >50% of files served). It turns out this script (which has grown organically and been coded in chunks since 2001) is horribly inefficient. I reduced the lines of code in the script by 55% to just 304 including all HTML and the weight of the page produced by 21% which will reduce my bandwidth usage by 4-5% which is cool and also has halved the SQL queries per page load on that page.

Efficiency is dull but I am hugely glad I did this and am looking forward to running through the rest of the site spring cleaning in Fall. If nothing else it will allow me to have a much smaller, easier codebase to maintain which is definitely good news!!!