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.

Walking down memory lane, I found a few older posts from a different site of mine.

I figured I'd copy those old entries in this blog, just to have everything in one place. And also to help flesh out my "archives" section a little more.

Turns out I actually used a different data type to store dates.

Ugh.

So I figure, "I'll just change the column type, surely my database is smart enough to figure these things out".

It wasn't.

Needless to say, I'll be adjusting the dates of the existing posts to try to make them somewhat match what they used to look like.