A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#101

Post by stickfigure » Sun, Nov 07, 2021, 4:44 PM UTC

kodt wrote: post #52

stickfigure wrote: post #44

Assuming this needs to be optimized for massive scale, just hash the values to a single indexed field. And use something other than an RDBMS. Put the hash in Redis and expire the key; your code simply does an existence check for the hash. You could probably handle gmail with a big enough cluster. That super-normalized schema looks terrible.

I too recommend using Redis in 2002.

Fair enough, but I think the issue here is recognizing that the underlying business operation is a hit test on an expiring hash key. You could have used MySQL as a key/value store or looked for something more specialized.

Re: A terrible schema from a clueless programmer

#102

Post by ipaddr » Sun, Nov 07, 2021, 4:46 PM UTC

adfgaertyqer wrote: post #72

maddynator wrote: post #32

Earlier quoted context omitted.

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…

You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB. We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 1…

Just to make this fun.

Adding an index now increases the insert operation cost/time and adds additional storage.

If insert speed/volume is more important than reads keep the indexes away. Replicate and create an index on that copy.

Re: A terrible schema from a clueless programmer

#103

Post by erikerikson » Sun, Nov 07, 2021, 4:48 PM UTC

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…

Clearly it worked.

However...

Consider the rows `a b c d e` and `f b h i j`. How many times will `b` be stored in the two formulations? 2 and 1, right? The data volume has a cost.

Consider the number of cycles to compare a string versus an integer. A string is, of course, a sequence of numbers. Given the alphabet has a small set of symbols you will necessarily have a character repeated as soon as you store more values than there are characters. Therefore the database would have to perform at least a second comparison. I imagine you're correctly objecting that in this case the string comparison happens either way but consider how this combines with the first point. Is the unique table on which the string comparisons are made smaller? Do you expect that the table with combinations of values will be larger than the table storing comparable values? Through this, does the combinations table using integer comparisons represent a larger proportion of the comparison workload? Clearly she found that to be the case. I would expect it to be the case as well.

Re: A terrible schema from a clueless programmer

#104

Post by Lewton » Sun, Nov 07, 2021, 4:49 PM UTC

darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

I’m happy the hn comments are nitpicking, I’m not particularly well versed in database schemas but while reading it I was going “???????” and it’s good to know I’m not going crazy

Re: A terrible schema from a clueless programmer

#105

Post by [deleted] » Sun, Nov 07, 2021, 4:50 PM UTC

throwawayFanta wrote: post #87

I think this blog fails to take into account that 2021 is not 2002. Computer Science is a much more formal/mainstream field of study now, and people don't operate "in a vacuum with no mentorship, guidance, or reference points.” Some comments on the previous blog post raised important questions regarding minimum understanding/knowledge of technology one utilizes as part of their day job. And I would agree that indexin…

[deleted]

Re: A terrible schema from a clueless programmer

#106

Post by ignoramous » Sun, Nov 07, 2021, 4:51 PM UTC

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…

> This post is bizarre ... Which is nonsense ...but from her description I would say it wasn't.

This is what the post is about.

And btw, if they hadn't normalized those tables, it'd instead have been pointed out to them that their "post is bizzare..."

Re: A terrible schema from a clueless programmer

#107

Post by HelloNurse » Sun, Nov 07, 2021, 4:52 PM UTC

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?

Normalization is important for deduplication, not only to index and compare a few short numbers instead of a few long string: those host names and email addresses are long and often repeated.

Re: A terrible schema from a clueless programmer

#108

Post by ryukafalz » Sun, Nov 07, 2021, 4:53 PM UTC

tptacek wrote: post #83

themgt wrote: post #81

Earlier quoted context omitted.

Also you could just store IPv4 as an unsigned INT directly, rather than strings in a different table and an unsigned INT as a foreign key.

I have regretted every single time I've gotten cute about storing IP addresses as scalars in SQL.

What issues did you run into? Having admittedly never done this before, it feels like whenever you need the dotted-quad IP you could just query:

    SELECT INET_NTOA(ip) FROM ips WHERE...

...in MySQL at least. (Which might be why MySQL has this function built in?)

I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue?

Re: A terrible schema from a clueless programmer

#109

Post by wdr1 » Sun, Nov 07, 2021, 4:53 PM UTC

Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games.

This was the first time regular people could go buy tickets for events & they had been lining up overnight at Bank of China locations through the country. We were down for over a day before we called it off. Apparently this led to minor upheaval at several locations in Beijing & riot police were called in.

We were pretty puzzled as we had an index and had load tested extensively. We had Oracle support working directly with us & couldn't figure out why queries had started to become table scans.

The culprit? A point upgrade to DBD::Oracle (something like X.Y.3 to X.Y.4) introduced subtle but in character sets. So the index required using a particular Unicode character set, and we were specifying it, but when it was translated into the actually query, it wasn't exactly the right one, so the DB assumed it couldn't use the index. Then, when all the banks opened & a large portion of very populous country tried to buy tickets at the same time, things just melted.

Not a fun day.

Re: A terrible schema from a clueless programmer

#110

Post by lisper » Sun, Nov 07, 2021, 4:53 PM UTC

> You know what it should have been? It should have been normalized.

No, it should have had a unique index on the first five columns.

(Oooh, I just hate it so much when someone gets up on their high horse and opens a blog post with a long condescending preamble about how some blogger got it wrong, and then they get it even wronger.)