About a year ago I ran across this presentation put together by Flickr regarding how they operate internally, and I recently had chance to re-read it when it was highly rated at programming.reddit.com. A few things really stuck with me — operating on PHP 4 and MySQL 4 was a shock, since I have trouble operating on anything less than PHP 5.2.0 anymore. But developers can code around that sort of thing, goodness knows we have, so that's not too unnerving.

What got me was the lessons I learned about scaling. Apparently, Flickr has denormalized some of their database tables. Now, I come from a very academic background regarding database design, so I was immediately expecting normalized data and was pretty confused when one of the slides mentioned that they duplicate data on purpose! It makes sense after they explain that having to JOIN on three or more tables is very time-consuming, but selecting all the data from one table isn't — even if it means having redundant data. Since I read that, I find myself looking at application responsiveness (especially re: stored procedures) differently than I had before. All that time spent on Flickr turned out to pay off in the end!

(As it turns out, we don't know what Primary Keys Flickr is using for their tables. As Bruce, our Database Overlord, points out, if they're using VARCHAR(50) fields, then of course JOINs are going to be expensive. Without knowing though, I'm willing to give them the benefit of the doubt.)