A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#191

Post by alksjoflkajsom » Sun, Nov 07, 2021, 6:19 PM UTC

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, and from columns, you'd still have no information about the "to" column).

Normalization does not mean "the same string can only appear once". Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. Now instead of "address1@foo.bar" in 20 different locations, you have "id1" in 20 different locations. There's been no actual "deduplication", but that's again not the point. Creating the extra tables has no impact on the normalization of the data.

Re: A terrible schema from a clueless programmer

#192

Post by topher_t » Sun, Nov 07, 2021, 6:22 PM UTC

zhoujianfu wrote: post #71

hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Phew.. I was reading this post thinking “is basically every table I’ve ever made wrong?!”

I definitely got a spike of imposter syndrome thinking, of fuck I've been in software this long and haven't learned this yet?!

Re: A terrible schema from a clueless programmer

#193

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

yuliyp wrote: post #181

globular-toast wrote: post #142

Earlier quoted context omitted.

Well she's clueless about what actually sped up this database query.

Without knowing which database engine (MySQL comes with more than one) she was using, nor the testing that she actually performed, what makes you say that?

I remember versions of mysql whose string indexing was sufficiently limited on every available backend that this would've been the correct change.

Hell, back in ... 2001? I think? I ended up making exactly the same set of changes to an early database design of mine, for pretty much the same stated reasons and with very similarly pleasant effects on its performance.

Re: A terrible schema from a clueless programmer

#194

Post by dasil003 » Sun, Nov 07, 2021, 6:25 PM UTC

otterley wrote: post #152

crispyambulance wrote: post #58

Earlier quoted context omitted.

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…

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 engineers who follow your advice will be scared to make mistakes and their development will languish compared to those who dive in and learn from their mistakes. As one gains experience it's easy to become arrogant and dismissive of mistakes that junior engineers make, but this can be extremely poisonous to their development.

Re: A terrible schema from a clueless programmer

#195

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

oconnor663 wrote: post #189

I think there's an easy "best of both worlds" take here: 1. The majority of the performance problem could've and probably should've been summarized as "you need to use an index". (Maybe there were MySQL limitations that got in the way of indexing back then? But these days there aren't.) 2. Everyone makes mistakes! New programmers make mistakes like not knowing about indexes. Experienced programmers make mistakes like…

Based on my own war stories, if it was an early '00s mysql then I expect "just throw an index at the varchar columns" would've helped but not nearly as much as what she actually did.

Re: A terrible schema from a clueless programmer

#196

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

hn_throwaway_99 wrote: post #136

jeroenhd wrote: post #94

Earlier quoted context omitted.

Based on this piece of old documentation I found [0] for MySQL 3.23 (the most recent version in 2002 as far as I can tell), certain types of indices were only available on certain types of engines. Furthermore, columns were restricted to 255 characters, which may be too short for some of the fields saved in the database. Modern databases abstract away a lot of database complexity for things like indices. It's true th…

The fact remains that whether the text column existed on her original table, or whether it was pulled out to a normalized table, literally all of the same constraints would apply (e.g. max char length, any other underlying limitations of indexing). The issue is that her analysis of what the issue was with her original table is completely wrong , and it's very weird given that the tone her "present" self is that it's…

Multiple-long-column compound indices sucked in old mysqls if you could even convince it to use them in the first place.

Being able to look up each id via a single-string unique index would've almost certainly worked much better in those days.

Re: A terrible schema from a clueless programmer

#197

Post by wvenable » Sun, Nov 07, 2021, 6:30 PM UTC

philliphaydon wrote: post #19

silisili wrote: post #12

Earlier quoted context omitted.

I think it depends a lot on the data. If those values like IP, From, To, etc keep repeating, you save a lot of space by normalizing it as she did. But strictly from a performance aspect, I agree it's a wash if both were done correctly.

Space is cheap now tho. Better to duplicate some data and avoid a bunch of joins than to worry about saving a few gb of space.

Using space to avoid joins will not necessarily improve performance in an RDBMS -- it might even make it worse.

Re: A terrible schema from a clueless programmer

#198

Post by rrrrrrrrrrrryan » Sun, Nov 07, 2021, 6:31 PM UTC

jameshart wrote: post #139

taeric wrote: post #59

Earlier quoted context omitted.

An index is often easily viewed as another table. So, should. (Some subtleties on projected values and such, but the point stands.)

Yes, the proposed ‘better’ structure basically amounts to building your own indexes. Normally, I’d assume it is better to use the RDBMS’s own engine to do that, don’t roll your own. There may well be some subtlety to the indexing capabilities of MySQL I’m unaware of though - could easily imagine myself making rookie mistakes like assuming that it has same indexing capabilities. So, to the post’s point - if I were wor…

As a former SQL Server DBA, most SQL Server DBAs are absolute gurus on 1 or 2 specific versions of SQL server, and massively ignorant about anything newer.

It's a strange role where, in order to do your job well, you kind of have to hyper-specialize in the specific version of the tech that your MegaCorp employer uses, which is usually a bit older, because upgrading databases can be extremely costly and difficult.

Re: A terrible schema from a clueless programmer

#199

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

codazoda wrote: post #147

hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

As a 25 year “veteran” programmer, I’m glad I’m not the only one that likes the original schema (with indexes added). A fully normalized table is a lot more difficult to read, troubleshoot, and reason about. You would need a pretty good query to poke around the data.

When I first started using postgres I was amazed at how much of what I thought was "database knowledge" was actually "working around limitations of the versions of mysql I'd been using" knowledge.

These days, sure, the original schema plus indices would work fine on pretty much anything I can think of. Olde mysqls were a bit special though.

Re: A terrible schema from a clueless programmer

#200

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

zhoujianfu wrote: post #71

hn_throwaway_99 wrote: post #11

This post is bizarre, precisely because there is nothing particularly wrong about the original schema, and the author seems to believe that the problem is that the column values were stored as strings, or that the schema wasn't in "third normal form". Which is nonsense. The problem with the original DB design is that the appropriate columns weren't indexed. I don't know enough about the problem space to really know i…

Phew.. I was reading this post thinking “is basically every table I’ve ever made wrong?!”

For modern databases, the extra normalisation is an incremental optimisation that you usually won't need.

But I think she's if anything been in the industry longer than I have and the versions of mysql I first ran in production were a different story.