Software, technology, sysadmin war stories, and more. Feed
Sunday, November 7, 2021

But wait, there's more

There's something to be said for writing a post late on a Saturday night. For some reason, it tends to bring out THE ONE more than any other time of the week. I wonder why that is.

For those of you with better self-control than me who can manage to not look, there's a raging comment section on HN going on about yesterday's post, and most of it is about terrible SQL practices. Basically, not only was I clueless 20 years ago, I definitely still am now.

I mean, you're not wrong... but how's the rest of that quote go?

Anyway, I figure the best thing for a raging trash fire is more fuel, and it turns out I left something out of yesterday's story! After it went online, I was reading it out loud to someone in my life, and that's when it hit me: I forgot all about the shit show that was how I SELECTed data back out of the database.

Recall that it turned into a situation where the individual components of the query (ip, helo, from, to) were now in their own tables, so they all had a number now - the "id" column. I figured, okay, I need to obtain those little suckers before I can build a query with them, so let's query!

SELECT id FROM m_ips WHERE m_ip = '...'
SELECT id FROM m_helos WHERE m_helo = '...'
SELECT id FROM m_froms WHERE m_from = '...'
SELECT id FROM m_tos WHERE m_to = '...'

So, yep, I fired off four queries to get four numbers, then built up a query, then fired that off as a fifth to get my results.

Five round-trips to the database when one would have done.

Did I mention that I obviously didn't know the first thing about JOIN back then? It took several more years for that to happen organically.

Go on, bag on me for being ignorant. I know what that really means.