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

241–250 of 493 posts

Re: A terrible schema from a clueless programmer

#241

Post by nzealand »

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…

> Normalization does not mean "the same string can only appear once".

It can, if you want to easily update an email address, or easily remove all references to an email address because it is PII.

Re: A terrible schema from a clueless programmer

#243

Post by soheil »

ryukafalz wrote: post #171
soheil wrote: post #123

Earlier quoted context omitted.

Because as shocking as this might sound like unlike the characters in the movie Matrix I don't have a built-in INET_NTOA function in my retina to see the string form of the IP addresses when glancing at table rows of a large table looking for patterns or a certain IP.

How often are you looking at the full contents of a table vs. looking at the results of a query that you could throw an INET_NTOA onto?

Many. You also expect me to remember how to spell INET_NTOA and on what fields to use it on. What if I wanted do a quick "SELECT * FROM"? I barely know how to spell English words what makes you think I'm going to remember how to spell INET_NTOA.

Re: A terrible schema from a clueless programmer

#244

Post by alksjoflkajsom »

nzealand wrote: post #241
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…

> Normalization does not mean "the same string can only appear once". It can, if you want to easily update an email address, or easily remove all references to an email address because it is PII.

That's not what normalization normally means, no. See eg wikipedia https://en.wikipedia.org/wiki/Database_normalization

Re: A terrible schema from a clueless programmer

#245

Post by ericbarrett »

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

Early InnoDB* had pretty strict limits on varchar indexes and was not the most efficient. I don't remember the details but it's entirely possible the single-table format Rachel described ran head on into those limitations.

Also remember indexes take space, and if you index all your text columns you'll balloon your DB size; and this was 2002, when that mattered a lot more even for text. Indexes also add write and compute burden for inserts/updates as now the DB engine has to compute and insert new index entries in addition to the row itself.

Finally, normalizing your data while using the file-per-table setting (also not the default back then) can additionally provide better write & mixed throughout due to the writes being spread across multiple file descriptors and not fighting over as many shared locks. (The locking semantics for InnoDB have also improved massively in the last 19 years.)

* Assuming the author used InnoDB and not MyISAM. The latter is a garbage engine that doesn't even provide basic crash/reboot durability, and was the default for years; using MyISAM was considered the #1 newbie MySQL mistake back then, and it happened all the time.

Re: A terrible schema from a clueless programmer

#246

Post by mst »

jeremyjh wrote: post #237
mst wrote: post #227

Earlier quoted context omitted.

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

Yes, we won't know because she didn't try it. And we know she didn't try it because the problem she described is table scans, not "indexed strings are somehow slow in MySQL circa 2002".

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 definitely being the most negative of the possibilities.

Re: A terrible schema from a clueless programmer

#247

Post by mst »

hn_throwaway_99 wrote: post #240
mst wrote: post #196

Earlier quoted context omitted.

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.

I used MySql a lot back then, had many multi-column indexes, and never had an issue. More importantly, given the degree of uniqueness likely to be present in many of those columns (like the email addresses), she could have gotten away with not indexing on every column.

Your first sentence was, I'm afraid, very much not a universal experience.

Your second is quite possibly true, but would have required experimentation to be sure of, and at some point "doing the thing that might be overkill but will definitely work" becomes a more effective use of developer time, especially when you have a groaning production system with live users involved.

Re: A terrible schema from a clueless programmer

#248

Post by enraged_camel »

IceDane wrote: post #47

The moral of this post really falls flat coming from this author, most of whose posts are snarky, passive-aggressive rants where she rants about someone's alleged incompetence or something similar.

This is a really weird ad hominem that is unrelated to the post itself.

Re: A terrible schema from a clueless programmer

#250

Post by cogman10 »

ericbarrett wrote: post #245
junon wrote: post #34

Earlier quoted context omitted.

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

Early InnoDB* had pretty strict limits on varchar indexes and was not the most efficient. I don't remember the details but it's entirely possible the single-table format Rachel described ran head on into those limitations. Also remember indexes take space, and if you index all your text columns you'll balloon your DB size; and this was 2002, when that mattered a lot more even for text. Indexes also add write and comp…

> Also remember indexes take space

Indexes take space, except for the clustered index.

An important distinction.

If the point of this table is always selecting based on IP, m_from, m_to, then clustering on those columns in that order would make sense.

Of course, if it's expected that a lot of other query patterns will exist then it might make sense to only cluster on one of those columns and build indexes on the others.

Post reply on HN