Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

A terrible schema from a clueless programmer

rachelbythebay.com

Post reply on HN
Print view View original

261–270 of 493 posts

Re: A terrible schema from a clueless programmer

#261

Post by damagednoob »

crispyambulance wrote: post #58
welder wrote: post #9

The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…

Yep. Folks are getting lost in the weeds discussing indexing of database tables. That's _totally_ beside the point here. The thing is, the first implementation was a perfectly fine "straight line" approach to solve the problem at hand. One table, a few columns, computers are pretty fast at searching for stuff... why not? In many scenarios, one would never see a problem with that schema. Unfortunately, "operating in a…

Yes and no and this post highlights a subtle issue with mentorship (which I think is important): Technology does not stand still. What was true in 2002, might not be true today. While adopting the naïve approach was detrimental back then, today databases recognise that this happens and provide easy workarounds to get you out of trouble that didn't exist back then.

I've experienced this just by switching languages. C# had many articles dedicated to how much better StringBuilder was compared to String.Concat and yet, other languages would do the right thing by default. I would give advice in a totally different language about a problem that the target language did not have.

As the song goes:

"Be careful whose advice you buy but be patient with those who supply it

Advice is a form of nostalgia, dispensing it is a way of fishing the past

From the disposal, wiping it off, painting over the ugly parts

And recycling it for more than it's worth"

Re: A terrible schema from a clueless programmer

#262

Post by [deleted] »

alksjoflkajsom wrote: post #224
twic wrote: post #211

Earlier quoted context omitted.

This is very slightly not quite true, because the HELO string and the remote address should go hand-in-hand.

You might be right (I don't actually know what a HELO string is, I don't know anything about SMTP :). I was just going off how the author presented the data, as a tuple of four completely independent things. Of course the main point still stands, that the two schemas are exactly as normalized as each other. Edit: rereading the original post, the author mentions that "they forged...the HELO"--so perhaps there was inde…

[deleted]

Re: A terrible schema from a clueless programmer

#263

Post by Izkata »

GauntletWizard wrote: post #254
alksjoflkajsom wrote: post #224

Earlier quoted context omitted.

You might be right (I don't actually know what a HELO string is, I don't know anything about SMTP :). I was just going off how the author presented the data, as a tuple of four completely independent things. Of course the main point still stands, that the two schemas are exactly as normalized as each other. Edit: rereading the original post, the author mentions that "they forged...the HELO"--so perhaps there was inde…

It's (somewhat) because the HELO is forged that there's no relationship between HELO and IP. The very first message of SMTP is "HELO ", hostname can either be a unique identifier (server1.company.com, etc.) or a system-level identifier (mta.company.com for all of your company's outbound mail agents, or in the case of bulk mailers they might use bulk.client1.com when sending as client1, bulk.client2.com, etc). But the…

> But there is/was no authentication on what you send as HELO

Yep, and that explains the "foobar" rows - those should have resolved to the same IP, except because there's no authentication that blocks it you could put gibberish here and the SMTP server would accept it.

> so correlating based on the hostname in HELO was questionable at best

Eh, spambots from two different IPs could have both hardcoded "foobar" because of the lack of authentication, so I could see this working to filter legitimate/illegitimate emails from a compromised IP.

Re: A terrible schema from a clueless programmer

#264

Post by mmaunder »

jjice wrote: post #208
mmaunder wrote: post #190

Oh well played!! I was setting up to give the author a hard time about being judgemental, particularly because storing IPs or UUIDs as strings is a mistake I've seen some pretty darn good devs make. Some folks just aren't super strong on schema design and performance but are great at other things. Plus MySQL kind of rocks. Fight me. There are some interesting optimizations that Percona has written about that may impr…

> Plus MySQL kind of rocks. Agreed. I prefer Postgres for personal projects, but MySQL is a fine database. Honestly, even a relational DB I wouldn't want to use again (DB2...) is still pretty solid to me. The relational model is pretty damn neat and SQL is a pretty solid query language. I wonder how many people disagree with that last part in particular...

It’s actually the core of all dev that involves any data storage, today. HN is both aspirational in tech and has a younger demographic so you won’t get any points here for mentioning anything SQL. Hence the downvotes I’m getting. But hey, it’s all good!!

Re: A terrible schema from a clueless programmer

#265

Post by quickthrower2 »

junon wrote: post #34
tomsmeding wrote: post #3

Why exactly would it be so bad to just put a suitable index on the table containing strings? The time complexity of the resulting search would be the same, so I assume there will be some constant factor slowdowns. Is it that indices over string fields are stored inefficiently on disk? (If so, can that not be fixed in the db engine directly?) Or is this fine today but wasn't fine 15 years ago?

Indexing existed 15 years ago. The article never mentions why indexing didn't solve this problem. Super weird take on the author's part...

Surely more than 15 years! 50 maybe?

Re: A terrible schema from a clueless programmer

#266

Post by musingsole »

GuB-42 wrote: post #172

The problem, I think is that most people, me included, don't really know what databases really do. There is a whole lot about optimizing procedural code, with a variety of tools, the dangers of premature optimization and the tradeoff with readability. Anyone with a computer science degree has heard about algorithmic complexity, caches, etc... But databases are just magic. You try things out, usually involving a CREAT…

> But databases are just magic.

100%!!!

I hate how developers talk about a "database" as a monolithic concept. It's an abstract concept with countless implementations built off of competing philosophies of that abstract concept. SQL is only slightly more concrete, but there's as many variants and special quirks of SQL dialects out there as databases.

Re: A terrible schema from a clueless programmer

#267

Post by dehrmann »

kelnos wrote: post #184
mewpmewp2 wrote: post #78

Earlier quoted context omitted.

I think if anything, all of it could've been put into a single indexed column since the query was AND ... AND ... not OR. So you could've had a indexed column of "fingerprint", like ip1_blahblah_evil@spammer.somewhere_victim1@our.domain And indexed this, with only single WHERE in the query. I don't understand at all how multiple tables thing would help compared to indices, and the whole post seemed kind of crazy to m…

Right, and when you do that, you don't even need a RDBMS. An key-value store would suffice. This essentially just becomes a set! Redis or memcached are battle-tested workhorses that would work even better than a relational DB here. But this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn'…

> this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn't be comfortable using in production.

Indeed; her problem looks to have been pre-memcached.

Re: A terrible schema from a clueless programmer

#268

Post by mst »

hn_throwaway_99 wrote: post #259
mst wrote: post #246

Earlier quoted context omitted.

Or she did try it and it didn't work - or researched the question and figured, quite reasonably based on my experience of mysql in that era, it probably wouldn't work - and kept the post to only the original problem and the final approach to avoid having even more technical details in a post that wasn't really about those. I agree that we don't know, but it seems a little unfair to her to treat every unknown as defin…

I really don't understand your general thrust here. MySql certainly had lots of issues in 2003, but being able to support multi-column indexes was not one of them. Her analysis is simply wrong - it is wrong now and was wrong then. Here is the doc from MySql v3.23: 7.4.2 Multiple-Column Indexes MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For cert…

As I said already: "mysql could index strings" and "using a compound index over four varchar columns would've worked out well" are significantly different propositions.

To be more verbose about it - there is an important difference between "can be created" and "will perform sufficiently well on whatever (likely scavenged) hardware was assigned to the internal IT system in question."

I wouldn't be surprised if the "server" for this system was something like a repurposed Pentium 233 desktop with a cheap spinning rust IDE drive in it, and depending on just how badly the spammers were kicking the shit out of the mail system in question that's going to be a fun time.

Re: A terrible schema from a clueless programmer

#269

Post by rmk »

pg_bot wrote: post #175
rmk wrote: post #157

Why not store the IP Address as a 32-bit number (IPv4 addresses)? Why store it as a string in the first place? This is something I did not quite get. Also, wouldn't it be better to split out the domain from the email address for ease of filtering? Also, how does performing joins give a performance advantage here. I'm assuming there would be queries to get at the IDs of at least one, but going up to 4, to get at the I…

You could store the IP as a 32 bit unsigned int. There's no issue with that, but I would probably recommend nowadays to use an internal cidr or inet type if your database supports that. It probably wouldn't be better to split the email address from the domain for filtering since email address formats tend to be a bit tricky. Joins give a performance advantage due to the fact that you aren't duplicating data unnecessa…

Yes, some of the databases support native ip address types that can be manipulated efficiently. Better to use that (like inbuilt json querying capabilities in postgres) than come up with your own.

It is still not clear how is it better to do up to 5 separate queries or maybe a few joins, than to store the strings and construct indices and such on them? Is the idea that the cost of possibly establishing a new socket connection for some concurrent query execution or sequentially executing 5 queries is still < possible scans of the columns (even with indexing in effect)? Also, even if you had integers, don't you need some sort of an index on integer fields to avoid full table scans anyway?

Re: A terrible schema from a clueless programmer

#270

Post by mwcampbell »

twic wrote: post #217
carapace wrote: post #56

Earlier quoted context omitted.

(It seems a lot of folks are getting nerd-sniped by the set-up and missing the moral of the story, eh?) I don't know the actual numbers, but it's been pointed out that at any given time something like half of all programmers have been doing it less than five years, for decades now. That, plus the strident ignorance of past art and practice, seem to me to bring on a lot of issues.

I don't think it's purely nerd-sniping. If your story is "at first you are bad, but then you get good", but your example is of a case where you did something fine but then replaced it with something worse, that rather undermines the story.

If even someone with Rachel's level of experience still doesn't know all the minutiae of database optimization, I think that just amplifies her point about the importance of mentoring novices.
Post reply on HN