kentcowgill.org/blog

Wow.

Never stop looking for ways to improve existing code.

I had noticed that after I added tagging capability to my photo gallery that:

1. Page loads seemed sluggish.
2. Tagging 300 or so photos produced a large list of tags.
3. Drawing the tags seemed the slowest, as I could watch them appear in the tag cloud.

I actually started going through about 150-200 of my photos and re-tagged them to remove silly and/or superfluous tags.

But then on Sunday I was browsing my del.icio.us network and saw an article that caught my eye about optimizing MySQL. Since MySQL is the database that powers much of what you see here, I figured I'd take a look to see if I could help shave off any execution time for my database access, in case it mentioned anything that I didn't already know.

One of the items near the end mentioned a tool called mytop. I downloaded and installed it and quickly learned two things.

1. My database is pretty low traffic.
2. Unless I'm hitting my photo gallery.

I used the qps(Queries per Second) mode and saw that when I hit the first page of the main gallery (which only shows 10 thumbnails with their captions in the href title by default), I had an astouding 500+ queries run in a single second! Meaning that every time through a page load, I was querying my database for captions for all (currently) 500+ images.

That's insane.

I already had some code in place that conditionally added the pictures I've looped through to my Template Toolkit $vars variable, but I was performing the caption lookup outside that condition - in essence running it for each and every picture, even though I'm only displaying 10.

I moved the caption query inside the condition, and noticed an immediate improvement - page generation time went from a half a second to a tenth of a second. Really, a fairly simple fix for an 80% speed increase.

So I'm in love with catalyst all over again.

I got tired of trying to figure out all these fancy-schmancy Object Relational Mapping database classes and trying to massage them to be able to understand my simplified database - which is a single table which is joined to itself for attaching a comment to a particular entry. I really didn't see any need for a separate table, since the structure of a comment doesn't really differ from the structure of a root post. Other than the fact that a root post doesn't have a parent.

Thinking that restructuring the database was just too much work, I just ripped out the ORM and created a model class that simply ran queries and returned the results.

Also, I added support for running my blog as a CGI and found that it was a really simple conversion, and only needed to update a few methods that I wasn't properly using before.