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

31–40 of 493 posts

Re: A terrible schema from a clueless programmer

#31

Post by acdha »

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.

It’s not that easy: you need to consider the total size and cardinality of the fields potentially being denormalized, too. If, say, the JOINed values fit in memory and, especially, if the raw value is much larger than the key it might be the case that you’re incurring a table scan to avoid something which stays in memory or allows the query to be satisfied from a modest sized index. I/O isn’t as cheap if you’re using a SAN or if you have many concurrent queries.

Re: A terrible schema from a clueless programmer

#32

Post by maddynator »

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…

One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available.

But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They are costly and will be costly if in tech industry, we continue to boot experienced engineers… the tacit knowledge those engineers have gained wi ll not be passed on and this means more people have to figure things out by themselves

Re: A terrible schema from a clueless programmer

#33

Post by dudeinjapan »

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…

I agree. In the "new" FK-based approach, you'll still need to scan indexes to match the IP and email addresses (now in their own tables) to find the FKs, then do one more scan to match the FK values. I would think this would be significantly slower than a single compound index scan, assuming index scans are O(log(n))

Key thing is to use EXPLAIN and benchmark whatever you do. Then the right path will reveal itself...

Re: A terrible schema from a clueless programmer

#34

Post by junon »

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

Re: A terrible schema from a clueless programmer

#36

Post by [deleted] »

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…

[deleted]

Re: A terrible schema from a clueless programmer

#37

Post by Moto7451 »

tyingq wrote: post #15

Feels like you could just concatenate and hash the 4 values with MD5 and store the hash and time. Edit: I guess concatenate with a delimiter if you're worried about false positives with the concat. But it does read like a cache of "I've seen this before" . Doing it this way would be compact and indexed well. MD5 was fast in 2002, and you could just use a CRC instead if it weren't. I suppose you lose some operational…

Yup, we do this at work for similar purposes and it works a-ok. We also have some use cases that follow the original “problem” schema and they work fine with the correct indices involved.

My guess is that in 2002 there were some issues making those options unappealing to the Engineering team.

When we do this in the realm of huge traffic then we run the data through a log stream and it ends up in either a KV store, Parquet with SQL/Query layer on top of it, or hashed and rolled into a database (and all of the above of there are a lot of disparate consumers. Weee Data Lakes).

This is also the sort of thing I’d imagine Elastic would love you to use their search engine for.

Re: A terrible schema from a clueless programmer

#38

Post by da39a3ee »

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…

OK so change the article's technical solution from "normalize" to "add index". That doesn't change the point of the article.

Re: A terrible schema from a clueless programmer

#39

Post by mwcampbell »

gfodor wrote: post #30
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…

The point is she was being hyper critical of her past schema to make a larger point.

And IIUC, the larger point, made by the twist ending, is that we shouldn't be so critical, but try to actually help newbies learn what we insist that they should know.

Re: A terrible schema from a clueless programmer

#40

Post by msqlqthrowaway »

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?

Post reply on HN