I’ve been working on my resume as I seek to apply for a job that’s a neat blend of multiple interests–managing web projects (even in my preferred LAMP environment), politics, and even a management potential. And as I do it, I’m remembering all the stuff I did at FIRST, and reflecting on how much better it could be.

I was “fluent” in SQL at the time, but didn’t know some of the neater functions of MySQL. For example, when I wrote the web management interface to the Stomatron, I didn’t know that I could make MySQL calculate times. So I’d retrieve a sign-in and sign-out time and use some PHP code to calculate elapsed time. This wasn’t terrible, really, but it just meant that I did more work than was necessary.

More significantly, I didn’t know about the MySQL query cache. (Actually, I don’t know when it was introduced… This was five years ago.) Some of the queries were quite intense, and yet didn’t change all that often. This is exactly where the query cache is indicated.

Worse yet, I really didn’t do much with the idea of caching at all. Being the stats-freak that I am, I had a little info box showing some really neat stats, like the total number of “man hours” worked. As you can imagine, this is a computation that gets pretty intense pretty quickly, especially with 30+ people logging in and out every day, sometimes repeatedly. Query caching would have helped significantly, but some of this stuff could have been sped up in other ways, too, like keeping a persistent cache of this data. (Memcache is now my cache of choice, but APC, or even just an HTML file, would have worked well, too.)

And, 20/20 hindsight, I don’t recall ever backing up the Stomatron box. (I may well be wrong.) Especially since it and our backup server both ran Linux, it’d have been trivial to write a script to run at something like 3 a.m. (when none of us would be around to feel the potential slowdown) to have it do a database dump to our backup server. (MySQL replication would have been cool, but probably needless.) If I were doing it today, I’d also amend that script to employ our beloved dot-matrix logger, to print out some stats, such as cumulative hours per person, and maybe who worked that day. (Which would make recovery much easier in the event of a catastrophic data loss: we’d just take the previous night’s totals, and then replay (or, in this case, re-enter) the day’s login information.)

I’m not sure it was even mainstream back then, but our website could have used a lot of optimization, too. We were admittedly running up against a really slow architecture: I think it was a 300 MHz machine with 128MB RAM. With PostNuke, phpBB, and Gallery powering the site, every single pageload was being generated on the fly, and used a lot of database queries. APC or the like probably would have helped pretty well, but I have to wonder how things would have changed if we used MySQL query caching. Some queries (like WordPress’s insistence on using exact timestamps in every one) don’t benefit. I wonder if phpBB is like that. I have a feeling that at least the main page and such would have seen a speedup. We didn’t have a lot of memory to play with, but even 1MB of cache probably would have made a difference. As aged as the machine was, I think we could have squeezed more performance out of it.

I’m still proud of our scoring interface for our Lego League competition, though. I think Mr. I mentioned in passing a day or two before the competition that he wanted to throw something together in VB to show the score, but hadn’t had the time, or something of that sort. So Andy and I whipped up a PHP+MySQL solution after school that day, storing the score in MySQL and using PHP to retrieve results and calculate score, and then set up a laptop with IE to display the score on the projector. And since we hosted it on the main webserver, we could view it internally, but also permitted remote users to watch results. It was coded on such a short timeline that we ended up having to train the judges to use phpMyAdmin to put the scores in. And the “design requirements” we were given didn’t correctly state how the score was calculated, so we recoded the score section mid-competition.

I hope they ask me if I have experience working under deadlines.