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

321–330 of 493 posts

Re: A terrible schema from a clueless programmer

#321

Post by dang »

bizzarojerry wrote: post #229
avl999 wrote: post #178

The responses in this thread are classic pedantic HN with the actual point whoooshing over so many people's head.

No I'd say the responses in this thread are classic pedantic HN with users like you taking the high moral ground and looking down on people who actually are trying to address the technical issues with a post in a technical forum.

We've banned this account for breaking the site guidelines. Creating accounts to do that with will eventually get your main account banned as well, so please don't!

https://news.ycombinator.com/newsguidelines.html

Re: A terrible schema from a clueless programmer

#322

Post by ricardobeat »

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

Re: A terrible schema from a clueless programmer

#323

Post by ghayes »

remram wrote: post #236
HelloNurse wrote: post #107

Earlier quoted context omitted.

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.

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.

Re: A terrible schema from a clueless programmer

#324

Post by tester756 »

harel wrote: post #7
fortran77 wrote: post #2

The "NoSQL" movement -- which I wisely rejected -- is also responsible for this.

All "movements" have some merit, or they wouldn't exist in the first place. Nobody starts a "movement" just because. It exists because it solves something. "NoSQL" was a solution to something. Today you can benefit from best of all worlds, and still NoSQL has a place and a use case, just like anything else that exists.

I will play devil advocate

>and still NoSQL has a place and a use case, just like anything else that exists.

What actually makes you believe that it's the NoSQL that has "some use cases" and relational databases are "default ones" instead of NoSQL/no-relational by default?

Re: A terrible schema from a clueless programmer

#326

Post by superjan »

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…

At my previous job, a particular database was designed using ascii strings fields for a particular field rather than unicode. If you then query with a string in unicode format, the database decided that the comparison should be done in unicode. The only way was to table scan and convert all ascii fields to unicode on the fly. It was found only in production.

Given that you mention China in your story, did GB 18030 have anything to do with your problems?

https://en.wikipedia.org/wiki/GB_18030

Re: A terrible schema from a clueless programmer

#327

Post by johannes1234321 »

ryandrake wrote: post #92
mewpmewp2 wrote: post #78

Earlier quoted context omitted.

I think if anything, all of it could've been put into a single indexed column since the query was AND ... AND ... not OR. So you could've had a indexed column of "fingerprint", like ip1_blahblah_evil@spammer.somewhere_victim1@our.domain And indexed this, with only single WHERE in the query. I don't understand at all how multiple tables thing would help compared to indices, and the whole post seemed kind of crazy to m…

Also, the "better" solution assumes IPv4 addresses and is not robust to a sudden requirements change to support IPv6. Best to keep IP address as a string unless you really, REALLY need to do something numerical with one or more of the octets.

Keeping IP addresses as string isn't trivial. You can write those in many ways. Even with IPv4. IPv6 just brings new variations. And when talking about migration to IPv6 you have to decide if you keep IPv4 Addresses as such or prefix with ::ffff:.

In the end you have to normalize anyways. Some databases have specific types. If not you have to pick a scheme and then ideally verify.

Re: A terrible schema from a clueless programmer

#328

Post by dan-robertson »

nickkell wrote: post #311
necovek wrote: post #291

Earlier quoted context omitted.

Foreign keys are naturally referencing primary keys, which have their own (unique) indexes by default. And there's some extra magic with CASCADE. But it seems like I am missing your point: care to expand on it so I can learn about the gotcha as well? What are you attempting to do, and what's making the performance slow?

If I've understood correctly it's the foreign key column itself that isn't indexed by default. Deleting a record then requires a table scan to ensure that the constraint holds

Example: if you delete a record from the customers table, you want an index on the foreign key in the orders table to delete the corresponding entries. This also means the performance of the delete can have the unfortunate property where the small work of deleting a single row cascades into the work of deleting many rows.

Re: A terrible schema from a clueless programmer

#330

Post by pgeorgi »

junon wrote: post #280
pgeorgi wrote: post #272

Earlier quoted context omitted.

I wouldn't bet on these indexes optimizing anything back then. MySQL was legendary that while implementing the necessary standards (to some degree) it was neither reliable nor efficient.

That's pretty beside the point though, the concept of indexing had existed for decades even prior to that.

The quote was "database optimization features" and the scope was "MySQL as of 2002".

Of course, even my old DBase II handbook talks about indexes - all that's old hat. MySQL had them, too.

MySQL also used to have a long-earned reputation as a toy database though, and MySQL in 2002 was right within the timeframe where it established that reputation. So yeah, you could add indexes, but did they speed things up (as in them being an "optimization feature")? Public opinion was rather torn on that.

Post reply on HN