PHP: mysql or mysqli?

By Jacob Allred | Published: February 3, 2010

As you may know, I’ve been spend­ing a lot of time try­ing to make the Fake Name Gen­er­a­tor as fast as pos­si­ble. I’ve started using a CDN, I’ve made minor code tweaks (like doing ++$i instead of $i++), added APC caching, and a bunch of other stuff, but still feel that the core of the FNG is a bit slow.

Cur­rently the code uses PHP’s mysql exten­sion. I’ve read in sev­eral places that mysqli is bet­ter, faster, more attrac­tive, gets more girls, etc, but have been unable to find any remotely recent bench­marks to prove it. I don’t care if the OO approach is cleaner look­ing, I just want it to be faster.

So I put together a bit of a bench­mark (very unsci­en­tific) to see if it is faster or not for my very spe­cific needs: I want to gen­er­ate 50,000 names as fast as pos­si­ble. Each name requires 4 SQL queries, so I end up mak­ing 200,000 queries (yikes!).

For my first attempt, I sim­ply replaced all of my pro­ce­dural mysql stuff with the OO mysqli equiv­a­lent. In other words, I con­nected to the data­base, ran each query, then got the result out of the result set. This had absolutely no improve­ment over my cur­rent method of get­ting data; some­times mysql was a sec­ond or two faster, some­times mysqli was a sec­ond or two faster.

For my sec­ond attempt, I used the mysqli::multi_query method to com­bine the 4 SQL queries per name down to a sin­gle trip to the data­base per name, drop­ping my total num­ber of trips to the data­base from 200,000 to 50,000. This yielded a dras­tic 66% speed improve­ment. There are sev­eral meth­ods in my name gen­er­a­tion class that gen­er­ate mul­ti­ple queries, so using this tech­nique will make the name gen­er­a­tor sig­nif­i­cantly faster.

Con­clu­sion: As a drop-in replace­ment for the stan­dard mysql exten­sion, mysqli does not appear to be any faster, even for lots of queries. How­ever, the mysqli::multi_query method makes it pos­si­ble to shave large per­cent­ages of time off of your script’s exe­cu­tion time.

Update (2−4−2010): I just ran an order for 10,000,000 names (with addresses, occu­pa­tions, etc). This process used to take about 25 hours to run on my server. With the new code it ran in only 15 hours. That is a 40% speed improve­ment for about 45 min­utes of effort! So my con­clu­sion is yes, mysqli can be much faster in cer­tain situations.

Posted in My Sites, Web Dev | Leave a comment

Save money AND make your site faster using a CDN

By Jacob Allred | Published: January 28, 2010

My most pop­u­lar web­site, the Fake Name Gen­er­a­tor, recently ran into two major problems:

  1. It was using too many server resources.
  2. It wasn’t as fast as I wanted it.

To solve these issues, I turned to Sim­pleCDN and their awe­some “mir­ror buck­ets”. Basi­cally you setup a bucket, point it at your base URL (in my case, http://www.fakenamegenerator.com), then update a few links on your web­site. Instead of link­ing to some­thing like http://www.fakenamegenerator.com/images/logo.png, I link to http://fake.name.generator.lg1x8.simplecdn.net/images/logo.png. When the vis­i­tor pulls up the new Sim­pleCDN URL, Sim­pleCDN will see if they have a cached copy of my image. If they don’t, they down­load it from my server then serve it to the visitor.

How does this help?

First, the web­site is faster for the vis­i­tor. The images are served up from a server that is geo­graph­i­cally near them and that is guar­an­teed to han­dle all the head­ers related to caching properly.

Sec­ond, my server doesn’t have to han­dle images or CSS for that site any­more. For the Fake Name Gen­er­a­tor, that is 260,000 requests per day that Apache doesn’t have to han­dle. This makes my server capa­ble of doing more with­out hav­ing to buy more mem­ory or get a faster CPU.

So the big ques­tion: how much does it cost? $0.039 per GB. For me, that means for the low low price $7.31 per year, they’ll han­dle 94,900,000 requests total­ing 187 GB.

The best part? They give you a free $15 account credit when you sign up, so I won’t pay a penny until 2012.

Check it out at Sim­pleCDN.

Posted in My Sites, Reviews, Web Dev | Leave a comment

Fix capitalized words in a MySQL table

By Jacob Allred | Published: January 27, 2010

I got a sup­port request today for the Fake Name Gen­er­a­tor. A vis­i­tor ordered US addresses, and some of the cities came back in all caps and some nor­mal (e.g., FOLSOM and Folsom).

After dig­ging into my code, I found that I was check­ing if a city name was in upper­case, and if so I was forc­ing it to low­er­case then run­ning PHP’s ucwords() on it (so FOLSOM -> fol­som -> Fol­som). This was hap­pen­ing on the web but not in bulk orders, so I never noticed it was hap­pen­ing until some­one told me.

Hav­ing recently started a cru­sade to make the Fake Name Gen­er­a­tor blaz­ing fast, I decided to fix the data­base rather than fix the PHP. But data­bases tend to be case insen­si­tive, so how to accom­plish this with­out hav­ing to pull every­thing into PHP, then save it back? Well, I found this handy MySQL query that lets you do case sen­si­tive queries:

select distinct city from ats__ng_areazipus where upper(city) = city collate latin1_bin;

That col­late latin1_bin some­how mag­i­cally makes things case sen­si­tive. So my query is show­ing me all the dis­tinct city names where the upper­case ver­sion of the city matches what is actu­ally in the table. Great! But how to go from FOLSOM to Folsom?

Search­ing on Google, I found this nifty MySQL user func­tion that lets you do some­thing sim­i­lar to PHP’s ucwords():

DROP FUNCTION IF EXISTS proper;
SET GLOBAL  log_bin_trust_function_creators=TRUE;
DELIMITER |
CREATE FUNCTION proper( str VARCHAR(128) )
RETURNS VARCHAR(128)
BEGIN
  DECLARE c CHAR(1);
  DECLARE s VARCHAR(128);
  DECLARE i INT DEFAULT 1;
  DECLARE bool INT DEFAULT 1;
  DECLARE punct CHAR(17) DEFAULT ' ()[]{},.-_!@;:?/';
  SET s = LCASE( str );
  WHILE i < LENGTH( str ) DO
    BEGIN
      SET c = SUBSTRING( s, i, 1 );
      IF LOCATE( c, punct ) > 0 THEN
        SET bool = 1;
      ELSEIF bool=1 THEN
        BEGIN
          IF c >= 'a' AND c <= 'z' THEN
            BEGIN
              SET s = CONCAT(LEFT(s,i-1),UCASE(c),SUBSTRING(s,i+1));
              SET bool = 0;
            END;
          ELSEIF c >= '0' AND c <= '9' THEN
            SET bool = 0;
          END IF;
        END;
      END IF;
      SET i = i+1;
    END;
  END WHILE;
  RETURN s;
END;
|
DELIMITER ;

So after adding that user func­tion to my MySQL data­base, I came up with the fol­low­ing query to fix my data:

update ats__ng_areazipus set city = proper(city) where upper(city) = city collate latin1_bin;

Is it super effi­cient? Prob­a­bly not, but I’m only run­ning it once, so effi­ciency isn’t an issue. And now I’ve been able to strip some con­di­tion­als and slow string manip­u­la­tion code out of my name gen­er­a­tor class! Yay!

Posted in My Sites, Web Dev | Leave a comment

Quick and Easy PHP WHOIS Script

By Jacob Allred | Published: January 19, 2010

I like to be able to type “whois somedomain.com” in my browser and have it bring up the WHOIS infor­ma­tion. The site I’ve been using has a captcha (annoy­ing) and has got­ten unre­li­able, so I wrote this quick and easy script to pull the full unfor­mat­ted WHOIS data for me:

<?php $d =  isset($_GET['d']) ? $_GET['d'] : ''; ?>
<html>
<head>
<title>WHOIS <?php echo $d; ?></title>
</head>
<body>
<pre>
<?php
if(!empty($d)){
    exec("whois ".escapeshellarg($d), $results);

    if(isset($_GET['debug'])){
        print_r($results);
    }

    $server = '';

    foreach($results as $result){
        if(stripos($result, 'whois server:')){
            $server = trim(str_replace('whois server:','',strtolower($result)));
        }
    }

    if(!empty($server)){
        passthru("whois -h ".escapeshellarg($server)." ".escapeshellarg($d));
    }else{
        foreach($results as $result){
            echo $result.PHP_EOL;
        }
    }

}
?>
</pre>
</body>
</html>

This script should work out-of-the-box for most Linux installations.

You can make the “whois somedomain.com” func­tion­al­ity work in Fire­fox by cre­at­ing a book­mark with a loca­tion of: http://www.yourdomain.com/whois.php?d=%s

I’d highly rec­om­mend you put this script in a pass­word pro­tected direc­tory to pre­vent abuse!

Posted in Web Dev | Leave a comment

Lock a Deadbolt without a Key

By Jacob Allred | Published: January 3, 2010

I found this fun Instructable today that shows you how to lock a dead­bolt with­out hav­ing the key. I can think of a few sit­u­a­tions where this could come in handy:

  • You are at a friend’s house and they leave you home alone but for­get to give you a key (or they give you the wrong one). You can’t leave the house unlocked, so that means you are stuck at home until they get back.
  • You can lock your co-worker’s or roommate’s door to confuse/anger them.

Hmm.. I sup­pose those are the only sit­u­a­tions I can think of where this would be useful…

Any­ways, all you need is some tape (clear pack­ing tape works great). Take about a yard of tape and fold it in half so that only a few inches on the end are still sticky. Securely attach the sticky end to the dead­bolt knob, hold on to the other end, step out­side, and shut the door. Pull the tape so the knob turns. Once the door is locked, tug on the tape so it breaks off so you don’t have a strip of tape hang­ing out of your door.

I tried it a few min­utes ago and it was very easy to do and very effective.

Posted in DIY, Random | Leave a comment

Jumbo Jenga

By Jacob Allred | Published: January 1, 2010

My friend Joey Novak recently posted about mak­ing some giant Jenga blocks, so I thought I’d give it a shot, too.

First, I needed to know what kind of wood to buy. Jenga blocks are 0.57″ x 1″ x 3″ (height x width x length). The eas­i­est wood to get would be either 2×4 (1.5″ x 3.5″) or 2×3 (1.5″ x 2.5″) studs. So if we upsize the Jenga blocks with a 1 to 3 ratio for the width/length, then our Jenga blocks would be either 1.5″ x 3.5″ x 10.5″ (2×4) with a height/width ratio of 0.43″ to 1″ or 1.5″ x 2.5″ x 7.5″ (2×3) with a height/width ratio of 0.6″ to 1″.

So what does all that mean? It means I should go with the 2×3 studs if I want the most  Jenga-like blocks.

Next I needed to know how much wood I needed. Jenga has 54 blocks, and since I decided to use 2×3 studs, that means I needed 405″ of lum­ber. Lowe’s sells 2×3×96″ studs for only $1.83 a piece, so I picked up 5 (only $9.15!), giv­ing me the abil­ity to make 60 jumbo Jenga blocks.

The last prepa­ra­tion needed was tools. I was short a few minor tools, but for a few dol­lars off of a gift card I got for Christ­mas, I ended up with the following:

  • Cord­less orbital sander with P80 and P220 sandpaper
  • Cord­less cir­cu­lar saw
  • Back­saw
  • Square
  • Clamps
  • Kitchen table chairs

Not ideal, but it works. I clamped a 2×3 to my kitchen chairs, marked the cuts using the square and a pen­cil, then sawed through with the cir­cu­lar saw. For some rea­son, my cir­cu­lar saw is just a pinch too small to cut through a 2×3, so I had to use the back­saw to fin­ish the cuts.

Next, I used the P80 sand­pa­per to smooth each sur­face and edge, and to remove any pen­cil marks or mark­ings that came on the lum­ber. This was a tedious task because I had to hold the sander in my lap as if it were a bench sander, and I kept run­ning out of bat­tery power. A corded bench sander would have made things a lot easier.

Last, a quick sand with the P220 to make each block nice and smooth so they’ll slide eas­ily while play­ing Jenga.

Because of my bat­tery issues, I only had time today to cut and sand one of my pieces of 2×3 (12 blocks), but here are the results so far:

After pulling the wrong block

A block cas­tle with Lego knights

I’m very excited to fin­ish the rest of the blocks! I’m going to cut the rest of the lum­ber tomor­row, and work on sand­ing it over the next few days.

Do you have any ideas for easy wood work­ing projects? Please let me know in the comments!

Update (1−2−2010): Today I cut the other 4 studs that I bought, so now I have 60 blocks total. I still need to sand 48 of the blocks, but it is a frigid 33° F out­side, and lately I’ve become a bit of a wuss when it comes to cold. Per­haps Mon­day or Tues­day when it warms up a little.

Posted in DIY | 1 Comment

Songbird

By Jacob Allred | Published: December 31, 2009

I have recently become a huge fan of Song­bird, a free multi-platform media player based on Mozilla’s XUL­Run­ner.

If you are any­thing like me, you may be ask­ing your­self what makes Song­bird spe­cial or worth your time. Why not just keep using iTunes, VLC, or Win­dows Media Player? Here are my rea­sons for choos­ing Song­bird over the competition:

  • It runs on my Win­dows 7 and Ubuntu 9.10 com­put­ers with the exact same inter­face and fea­ture set.
  • Highly con­fig­urable playlists. For exam­ple, I can make a dynamic playlist that only shows songs that: have been rated 3 or more stars, haven’t been skipped more than 5 times, are at least 3.5 min­utes long, and were pro­duced between 1990 and 2000.
  • With a minor tweak, Song­bird will save my song rat­ings in the MP3 file itself. This means I can put a song in my Drop­box, rate it at work, and by the time I get home the rat­ing will be there, too.
  • Speak­ing of tweaks, Song­bird is as tweak­able and con­fig­urable as Fire­fox. You can get exten­sions, themes, adjust inter­nal set­tings that most peo­ple don’t care about, change how the title bar works.
  • Has an inter­nal web browser that knows when you’re on a page with music. This means you can quickly and eas­ily down­load free music from places like Last.fm and have your new songs instantly imported into your music library.
  • Also because it has an inter­nal web browser, I can open Slacker or Pan­dora in Song­bird and save myself hav­ing an extra tab in Fire­fox. (Bonus: If Flash starts act­ing up in Ubuntu, I just have to restart Song­bird and not my whole browser)

Read More »

Posted in Free Stuff, Reviews | Leave a comment

The Shepherds

By Jacob Allred | Published: December 21, 2009

Yes­ter­day at church our Pri­mary watched Luke 2, a short video about Christ’s birth. Part of the video shows angels com­ing to tell shep­herds of the savior’s birth. Dur­ing this part of the movie I thought to myself that it was a bit ran­dom that some shep­herds saw angels when Christ was born. What made them so spe­cial? Why was it recorded in the Bible? Why weren’t other peo­ple vis­ited by angels at this time?

Well, I found a pos­si­ble answer today on the Tem­ple Study blog:

…the shep­herds who were abid­ing by their flocks in the fields were per­haps watch­ing over tem­ple sheep, sheep that were being bred and pro­tected to be sac­ri­ficed at the tem­ple in Jerusalem.

Very inter­est­ing, no? What is also very inter­est­ing is the arti­cle also goes into some detail about which stable/manger Christ was at. I don’t want to ruin the awe­some end­ing for you, so read the full arti­cle here.

Posted in Religion | Leave a comment

Automatic JS Broken Image Replacement

By Jacob Allred | Published: December 17, 2009

It’s a sad fact of life, but some­times web­pages have bro­ken images. Wouldn’t it be nice if there were an easy way of auto­mat­i­cally replac­ing these bro­ken images?

for (i = 0; i &lt; document.images.length; i++) {
  var img = document.images[i];
  img.onerror = function (evt) {
    this.src = "broken-image.gif";
  }
};

This snip­pet of JavaScript will find all your images and replace the bro­ken ones with broken-image.gif. Pretty spiffy, huh?

You can also do some­thing like this to apply it to indi­vid­ual images:

<img src="good-image.gif" onerror="this.src='broken-image.gif';">
Posted in Web Dev | Leave a comment

Hulu Ad Failures

By Jacob Allred | Published: December 11, 2009

I watch a lot of Hulu, and gen­er­ally I don’t really mind an ad or two. How­ever, I hate untar­geted ads. For exam­ple, ads for Mono­stat or birth con­trol pills. I’m a guy. Hulu knows I’m a guy (I checked, its in my Hulu pro­file). Why in the world are they show­ing me com­mer­cials for these things? Have I watched too many chick flicks or some­thing? Does any­one else have this problem?

Posted in Movies & TV, Rants | Leave a comment