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

331–340 of 493 posts

Re: A terrible schema from a clueless programmer

#331

Post by dan-robertson »

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?

I think the article describes the solution that was used and not necessarily the best solution. If they made the “obvious” mistake in the first case they mightn’t arrive at the best solution in the second. Adding the single index and using the four tables have the same worst case complexity for reads, O(log n), with different constants and massively beat scanning at O(n).

It may be that the second solution wasn’t the best, or that it was better for write performance or memory usage—this was 2002 after all. It may also be the case that the cardinal it’s of some columns was low in such a way that the four table solution was better, but maybe getting the columns in the right order for a multi column index could do that too.

Either way, I don’t think it really matters that much and doesn’t affect the point of the article.

Re: A terrible schema from a clueless programmer

#332

Post by nightski »

damagednoob wrote: post #261
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…

Yes and no and this post highlights a subtle issue with mentorship (which I think is important): Technology does not stand still. What was true in 2002, might not be true today. While adopting the naïve approach was detrimental back then, today databases recognise that this happens and provide easy workarounds to get you out of trouble that didn't exist back then. I've experienced this just by switching languages. C#…

That might be true if you just take blanket advice. The key is to find out why say StringBuilder is better than String.Concat. If you understand the implementation details and tradeoffs involved, this makes the knowledge much more applicable in the future. The core concepts in technology do not move nearly as fast as individual projects, libraries, frameworks, languages, etc...

Re: A terrible schema from a clueless programmer

#333

Post by wpietri »

wdr1 wrote: post #109

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 wer…

Could you say more about why that was the culprit and not some problem with the load testing? For the Olympics, it seems like the most important test is "What happens when we launch?" That's the kind of thing I'd run on every commit.

Re: A terrible schema from a clueless programmer

#334

Post by notreallyserio »

ricardobeat wrote: post #322
nlitened wrote: post #279

> The rub is that instead of just being slow, it also cost a fair amount of money because this crazy vendor system charged by the row or somesuch. So, by scanning the whole table, they touched all of those rows, and oh hey, massive amounts of money just set ablaze! Why _the hell_ is nobody mentioning that using a database that charges per row touched is absolute insanity? When has it become so normal that nobody ment…

You'll probably enjoy reading this article that was on the front page yesterday: https://briananglin.me/posts/spending-5k-to-learn-how-databa...

Nice. I was thinking of an earlier post involving cloud storage buckets with no index.

Re: A terrible schema from a clueless programmer

#335

Post by remram »

ghayes wrote: post #323
remram wrote: post #236

Earlier quoted context omitted.

That's not what normalization is. You're thinking of deduplication or compression. This table is normalized as per usual database terminology.

But if it’s guaranteed to be 1:1, why? The two implementations (normalized and denormalized) in that case should be completely isomorphic, quirks of the DB engine aside.

Why what? I don't understand what you are asking.

Re: A terrible schema from a clueless programmer

#336

Post by mewpmewp2 »

hoppla wrote: post #117
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…

I usually store IPv4 as unsigned int, and IPv6 as two 64 bit unsigned ints. But supporting both variants require that I have an a table for each type and a glue table to reference the record in the correct table. Storing simple stuff quickly turns complicated…

Is there much point in optimising this as opposed to keeping it in strings, at what QPS do you think it would matter and how much?

Re: A terrible schema from a clueless programmer

#337

Post by merb »

mst wrote: post #268
hn_throwaway_99 wrote: post #259

Earlier quoted context omitted.

I really don't understand your general thrust here. MySql certainly had lots of issues in 2003, but being able to support multi-column indexes was not one of them. Her analysis is simply wrong - it is wrong now and was wrong then. Here is the doc from MySql v3.23: 7.4.2 Multiple-Column Indexes MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist of up to 16 columns. For cert…

As I said already: "mysql could index strings" and "using a compound index over four varchar columns would've worked out well" are significantly different propositions. To be more verbose about it - there is an important difference between "can be created" and "will perform sufficiently well on whatever (likely scavenged) hardware was assigned to the internal IT system in question." I wouldn't be surprised if the "se…

you know for performance the new table would've needed a string index aswell?

Re: A terrible schema from a clueless programmer

#338

Post by emilsedgh »

twic wrote: post #217
carapace wrote: post #56

Earlier quoted context omitted.

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

> but your example is of a case where you did something fine but then replaced it with something worse

They way I see it is that it was a case of something that wasn't working, then replaced with something that was working.

I don't know what your metrics about "good" or "bad" are but eventually they got a solution that covered their use cases and the solution was good enough for stakeholders and that is "good".

Re: A terrible schema from a clueless programmer

#339

Post by Cpoll »

unethical_ban wrote: post #149
Cpoll wrote: post #91

Earlier quoted context omitted.

I don't buy it. If the only point was managing rookie errors, etc., the blog post shouldn't have been 15~ paragraphs of technical discussion and 2 paragraphs at the bottom of "my point is ..." You can't advocate that people just ignore 80% of the article because they're "not the point of the post." I'm sure a good chunk of tech workers are worried that when an influential blogger writes something like this a couple h…

It's a narrative. A story. She told it in that way to lure people who get hooked on technical discussion, and then make the point that they probably would have missed or ignored. Without a narrative example, she could have just said "I made mistakes too, live and learn" but she chose to provide an example before presenting the thesis. And hey, what's so bad about people learning about 3NF? Are you not supposed to kno…

Sure, I don't mind that the hook, and controversial posts often create the most educational discussion. I only take exception to the idea that we shouldn't debate it because it's "not the point." (specifically for technical advice; being over-pedantic is a thing).

I certainly don't mean to criticize the author or say that they shouldn't have posted the article. If articles needed to be 100% correct then no-one has any business writing them. And it's still open to debate whether the article is even wrong or misleading.

> Are you not supposed to know what that is until you're some mythical ninth level DBA?

I think the world might be a better place if people _did_ wait until they were ninth level DBAs :p

Jokes aside, I have no problem with people learning 3NF. I'm more concerned that people can be too quick to take this sort of rule-of-thumb advice to heart (myself included). And in my opinion, database optimization is too complex to fit well into simple rules like "normalize when your query looks like this." My only 'rules' for high-frequency/critical tables and queries is "test, profile, load test, and take nothing for granted." But I know just enough to know that I don't know enough about SQL databases to intuit performance.

Post reply on HN