A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#211

Post by twic » Sun, Nov 07, 2021, 6:44 PM UTC

alksjoflkajsom wrote: post #191

People in the comments are getting (rightfully) outraged about the poor understanding of indexing, but I'm a little surprised that everyone here doesn't seem to understand normalization either. The original schema is perfectly normalized and is already in 3NF: none of the columns shown has a dependence on any of the other columns outside of the primary key (in other words, if you knew eg the values of the ip, helo, a…

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

Re: A terrible schema from a clueless programmer

#212

Post by beering » Sun, Nov 07, 2021, 6:44 PM UTC

carapace wrote: post #56

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…

(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.

> (It seems a lot of folks are getting nerd-sniped by the set-up and missing the moral of the story, eh?)

The narrative should lead to the conclusion. If I told you the story of the tortoise and the hare in which the hare gets shot by a hunter, then said the moral is "perseverance wins", you'd be rightfully confused.

Re: A terrible schema from a clueless programmer

#213

Post by [deleted] » Sun, Nov 07, 2021, 6:45 PM UTC

carapace wrote: post #56

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…

(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.

[deleted]

Re: A terrible schema from a clueless programmer

#214

Post by mjevans » Sun, Nov 07, 2021, 6:45 PM UTC

jerjerjer wrote: post #23

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?

Yeah, while this is not optimal (ip should be converted into integer, time should be ts), the table would be small (as old entries could be safely deleted). The only real issue is the lack of indices. Also is helo field even needed?

Yes. A proper mail implementation won't randomize it, so it should be consistent. Depending on the design of the validation stages it might only need a case-normalized lookup to the string index table and that id as part of the final multi-column unique index, and in that last case only to convey to the database the expectations to optimize around (and require the data adhere to).

Re: A terrible schema from a clueless programmer

#215

Post by jeremyjh » Sun, Nov 07, 2021, 6:45 PM UTC

msqlqthrowaway wrote: post #40

ccleve wrote: post #8

Sorry, no. The original schema was correct, and the new one is a mistake. The reason is that the new schema adds a great deal of needless complexity, requires the overhead of foreign keys, and makes it a hassle to change things later. It's better to stick the the original design and add a unique index with key prefix compression , which all major databases do these days. This means that the leading values gets compre…

> all major databases do these days Did everyone on HN miss that the database in question was whichever version of MySQL existed in 2002?

It actually doesn't matter. Even simple indexes would have made that schema work just fine, and MySQL could certainly index strings in 2002.

Re: A terrible schema from a clueless programmer

#216

Post by lamontcg » Sun, Nov 07, 2021, 6:46 PM UTC

egeozcan wrote: post #185

wwweston wrote: post #180

Earlier quoted context omitted.

Old enough some people under 35 may not have encountered it… ;)

I feel like that phrase has been there forever, and I'm exactly 35. Will this happen more often as I grow older? Ugh. Feels weird. Maybe also a bit depressing.

It was weird seeing all the spongebob squarepants memes take over the internet when I was too old to ever grow up with that. I turned 28 in 1999 when that first aired. That was my "holy shit I'm so old" moment when that finally trickled up into my awareness as someone nearly turning 40 or so.

Re: A terrible schema from a clueless programmer

#217

Post by twic » Sun, Nov 07, 2021, 6:46 PM UTC

carapace wrote: post #56

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…

(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.

Re: A terrible schema from a clueless programmer

#218

Post by jeremyjh » Sun, Nov 07, 2021, 6:46 PM UTC

mst wrote: post #210

newaccount74 wrote: post #179

Earlier quoted context omitted.

I think she inadvertently made a different point, which is that even experienced developers sometimes misunderstand the problem and make mistakes. Or an even better argument: you don't need to actually understand the problem to fix it, often you accidentally fix the problem just by using a different approach.

I would argue instead that this comment thread is making the point that people forget that things that work now wouldn't've worked then and design decisions have to be made based on the database engine you're running in production.

MySQL could index strings in 2002. It would have worked fine.

Re: A terrible schema from a clueless programmer

#219

Post by [deleted] » Sun, Nov 07, 2021, 6:47 PM UTC

dasil003 wrote: post #194

otterley wrote: post #152

Earlier quoted context omitted.

I’d be a lot more sympathetic if the major RDMSes didn’t have outstanding and thorough reference manuals or that there weren’t a mountain of books on the subject that cover, among other things, the topic of indexing and its importance. MySQL’s manual, for example, has covered this subject from the very beginning: https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html (I don’t have the 3.x manuals handy but it was…

I couldn't disagree more with this comment. No amount of reading documentation teaches you how to build production systems. You progress much faster by getting your hands dirty, making mistakes, and learning from them. The challenge of writing good software is not about knowing and focusing on the perfection every gory detail, it's about developing the judgement to focus on the details that actually matter. Junior en…

[deleted]

Re: A terrible schema from a clueless programmer

#220

Post by mst » Sun, Nov 07, 2021, 6:48 PM UTC

Jolter wrote: post #188

mceachen wrote: post #159

Actually, the Correct Answer is a bloom filter. (And, yes, we had math in the early 2000s.) Snark aside, I'm frustrated for the author. Her completely-reasonable schema wasn't "terrible" (even in archaic MySQL)—it just needed an index. There's always more than one way to do something. It's a folly of the less experienced to think that there's only One Correct Way, and it discourages teammates when there's a threat of…

To be fair, the “correct way” to do databases at that time was to use third normal form. Putting indices on string columns would have been considered a hack, much like using MySQL was.

Plenty of databases at the time had pretty lame string indexing because it simply wasn't something people relied on if they wanted performance, and memory sayeth mysql's were both not particularly efficient and had some nasty technical limitations.

On the mysql she was using, breaking things out so it only needed to index ints was almost certainly a much better idea. On anything I'd be deploying to today, I'd start by throwing a compound index at the thing with the expectation that'd probably get me to Good Enough.