Divine Lunacy

Last Friday, I had to run some complicated SQL queries at work. I was dealing with 300k rows of history items that I had to select based on an aggregate function. Unfortunately, WHERE clauses cannot contain the result of an aggregate function. Having clauses are like where clauses, but they’re applied last, and without optimization–and they can contain the result of an aggregate function. Here’s an example:

mysql> SELECT `col1`, COUNT(distinct `col2`) as as distcol2 , COUNT(distinct HOUR(`date`)) as hours FROM `sometable` WHERE foo GROUP BY `col1` HAVING `hours` = 2 and `distcol2` = 2 order by rand() DESC limit 100;


While reading my Gmail yesterday, I saw a sponsored link titled “The PHP Doctor“. Being a self-proclaimed PHP expert myself, I was interested to see exactly how good this PHP Doctor is. He has a blog–a PHP tips and tricks blog. The first post was about functions, and their use in writing modular code. Although the post used a solid example of functions, I couldn’t help but notice a few sloppy performance issues. The following are tips that I have created after reading that post, and the second post on the page.

1. Where possible, do not use functions in the entrance condition for loops. Consider the following example: (From The PHP Doctor*)
   for( $x = 0 ; $x < strlen( $InString ); $x++ ) {
    if (strstr( $OKChars , $InString[$x] ) ) $OutString .= $InString[$x];
}

   Note that for every iteration of that loop, the value strlen( $InString ) remains the same. However, on each iteration of the loop, the function is run. To avoid running this function each iteration, the value should be assigned to a variable before the first iteration:
   
for ( $x = 0,$c = strlen( $InString ); $x < $c; $x++) {
    if ( strstr( $OKChars , $InString[$x] ) ) $OutString .= $InString[$x];
}

2. ++$i, $i++, $i += 1, and $i = $i + 1 all have the same basic effect, but they are indeed different. Using using $i++ or ++$i is faster than using$i += 1 or $i = $i + 1. For those who don’t remember, $i++ and ++$i are respectively, the post increment and the pre increment operators. The difference between the two is that pre increment increments $i, then returns the incremented value, whereas post increment returns the value of $i, then increments $i.
3. Although PHP doesn’t really care, try to avoid implicitly casting numerical types to strings when it is not necessary. Consider a function that takes a numer, $Number, and rounds it to the nearest hundredth. (Note: this can be done via PHP’s round function: round($Number,2)). Here is an example from The PHP Doctor*:
   
function roundToTwo( $Number ) {
    //pow(10, -3) = .001
    $Number += pow( 10 , -3 ); //Make sure the thousandths place is populated
    $Number = round( $Number * pow( 10 , 2 ) ) / pow( 10 , 2 );
    //Make sure the thousandths place is populated
    $Number += pow( 10 , -3 ); //pow() returns float or int (false on error)
    //Take the decimal point, and the following two characters.
    $Number = substr( $Number , 0 , strpos( $Number , '.' ) + 3 );
    return $Number;
}

   Rounding is a mathematical function, but the preceding function relies on properties of strings. One downside of this is performance; another is formatting: roundToTwo() will return a string, and because strings have no concept of significant digits, non-significant digits will be returned. (return value could be something like 123.00) Anyway, with a little more thought, we can find a way to do this without using strings. When round is passed only one argument, it will round the argument to the nearest integer. It follows that round($Number*100)/100 will function the same as roundToTow($Number). But suppose we want to avoid using round()… Recall that if we are rounding to n decimal places, we round up when the digit in the (n+1)th decimal place is greater than or equal to 5, and down otherwise. Adding 5 to this digit will accomplish this. Thus, we can now use the floor() function instead of round(): floor($Number*100 + .5)/100.
4. In the previous example, not the use of pow(x,y) where x and y are constant. For better performance, calculate the value pow(x,y), and use that value in your program. In some cases, this will also lead to more readable code.

In the past, writing has not been my strong point, though some of my former instructors may disagree. I have always struggled through writing assignments, especially those that were literary in nature. Research papers, or papers that involved writing about and interpreting factual information have, as far as writing goes, been my strong point. Though I have never gone so far as to create a portfolio, I have archived all of my mathematical reports, some of my creative pieces, and in general, those pieces that I actually liked. (Which aside from the math reports, number few.)

As a quiet, shy, and very independent person, I have never liked peer editing, and don’t tend to take it seriously. This is partially because I feel uncomfortable pointing out others’ grammatical errors and suggesting revisions to wording, et cetera. (unless it is someone with whom I am friendly.) In the past, this has led my instructors to repremand me for my blatant disregard for the peer editing process. As someone who’s bedroom’s entropy exceeds a large room full of helium gas, portfolios are not exactly my forte. My room’s disorder has overflowed into my desk, my backpack, and most importantly, my mind. One folder has always been enough for all my classes. Inside the folder, chaos prevails. The papers therein are organized on demand, and until there is such demand, disorder shall endure. December fourteenth will likely find me up into the wee hours of the night, anxiously trying to complete my portfolio.

It started as a quest to find a piece of paper that details the channels for each row of house lights. Now, it is as though I am bringing closure to the last four years of my life. I have gone through several huge stacks of paper and found things dating as far back as my Freshman year. I have chemistry notes from my sophomore year, Spanish notes from my freshmen and sophomore years, English notes from my sophomore year, and history notes from my sophomore year. (I still haven’t found that stupid paper. Every time I write that stuff down, I end up losing the paper and writing it all down again next time I need it.) “Summer of ‘69″, by Bryan Adams just came on shuffle. We played that song during the slide show at our eighth grade class supper. I have thus far manged to Distrubute the last four years in to four folders, and one huge pile, as follows: High School Math, High School Math - POW’s, Math League (I saved all of the papers.), Stuff from High School, and the “Why didn’t I throw this crap away a long time ago” pile.

I *secretly* never completed some of those essays for the superintendent. Oops. My bizzle.

Once upon a time, someone messed the board up. This has actually happened many times, but it was even more messed up this time. Actually, it wasn’t the board, but we didn’t know that before we spent an hour and a half playing with it. Anyway, the night we went to fix it, we opened up the back of the board and installed a hardware upgrade kit that had been sitting around in my house for a while. We did not have permission to do this. We were not qualified personnel. Until now, very few people knew about this. That same night, after discovering ze rack (you may have heard of this.), we reprogrammed all of the submasters. As part of this, I wrote down which rows of houselights are controlled by which channels. (channels 97 and 98 do rows 1 & 2, respectively, but it’s a little screwy after that. 113 is the exit lights.). I lost the paper. ::sigh:: Happens every time. I’ll guess I’ll never completely complete that diagram. This whole night has been named operation cupcake. We also played with macros. They’re fun. OMG, I wonder if we ever tried to make a recursive macro. I wonder how one would stop that…

Graduation. It always seemed so far off in the future that I managed to convince myself it would never come. Now it is in past. Graduated. I spent every day of my high school career awaiting the end. Now that the end has come and gone, I find myself slightly depressed. About 169 seniors graduated last night. I probably won’t see many of them again, but I didn’t like most of them anyway. I’ll be in touch with those who are worth staying in touch with. What distresses me is that I am an alumnus. For four years, high school was the only life I knew. When summer came, I knew what September would hold. When September came, the student entrance always welcomed me. Though I may walk through those doors again, I will never again do so as a student. Those years have come and gone as quickly as …. I no longer know what September holds, but I have come to accept this. The student entrance is closed to me, but in front of me lies a new path. This may be an end to a lifestyle that I have come to know and love, but it is also the beginning of something greater. I’LL BE BACK!

In other news, I pwned Bashful at Badminton, but he’s afraid to admit it. (he can’t admit to being pwned by the fat kid.)

DO NOT eat the crumbs that are in the bottom of the bag. OUCH. You have been warned.

Two weeks ago: God, have mercy upon me. Just let it end already. I hate school.
Now: OMG, I’m leaving. How sad. :’(… OMG, I CAN’T TAKE IT ANYMORE!!!

Here is the official count:

Bashful would do anything to get his hands on this will. Too bad he’ll never think to look here. (He might stumble upon it, but I’m sure he won’t actually look here.)

I, Greg Bowser, willingly bequeath to Daniel “Bashful” Ketcham, two-thirds of my leet-ness; to Christopher “Fairy” Bailey-Gates, The Official SAT Study Guide; to the aforementioned Dwarf and Fairy, a 250-page technical manual and free technical support for two years; to Kayla Faith Caron, my position as Prime Minister of the Mike Empire and protection in the upcoming merger; and to each of the aforementioned heirs, the infinite wisdom of Robert Frost: stay gold.

1. I was trying to put a quiet ban on an IRC user today, but I accidnentally typed /mode #divinelunacy +b ~q: *!sticks1988!@*. The space between the : and the* caused the server to interpret it as a ban on *!*@*, which is everyone in the channel. When I tried to correct myself, I ended up tying /mode #divinelunacy +b ~q*!sticks1988!@*. (I think that’s what I typed anyway…) The server interpreted that as #divinelunacy +b ~q:*!*icks1988!@*. I just gave up after that. (I meant /mode #divinelunacy +b ~q:*!sticks1988@*.)
2. I wanted to add 200 rows to the Mafia Returns database… I have my reasons… I typed something to the effect of
   
   
<?php
include 'includes/db.php';
for ($i = 1; $i <= 300;)
//mysql insert query here.
?>

   OOps… An infinite loop. It added 30 MB of data to the database and caused 25 minutes of downtime. Oops.