A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#311

Post by nickkell » Sun, Nov 07, 2021, 9:56 PM UTC

necovek wrote: post #291

hinkley wrote: post #230

Earlier quoted context omitted.

The one that always got us is much more mundane. Deleting a row requiring an index in every table with a FK to avoid tables scans. Near as I can tell, we assume there is some bit of magic built into the foreign key concept that handles this for us, but that is not the case.

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

Re: A terrible schema from a clueless programmer

#312

Post by 08-15 » Sun, Nov 07, 2021, 9:58 PM UTC

mixmastamyk wrote: post #297

08-15 wrote: post #234

Earlier quoted context omitted.

In this blog post, senior engineer Rachel talks nonsense about normalization and promotes a bafflingly complicated solution as superior to a simple one, without identifying the actual fix, which appears to have happened accidentally. In other words, with enough empathy and patience, a clueless rookie can grow into a clueless senior engineer! Rachel usually makes more sense than that. That's why people are nitpicking…

Hah, pretty accurate and kinda funny, but could be nicer. I still make mistakes.

Meanwhile, Rachel posted an update. Apparently, the whole section about how "the system got reworked" doesn't describe the proper solution, but her own (Rookie Rachel's) attempt to fix it.

And in that context, everything makes sense.

Re: A terrible schema from a clueless programmer

#313

Post by ummonk » Sun, Nov 07, 2021, 9:59 PM UTC

I was preparing to rant about the tone that the post was taking towards the "clueless programmer" until I got to the end. Well done...

Still not sure why adding an index didn't work instead of breaking out into multiple tables indexed by IDs.

Besides, this use case screams for "just store the hashes and call it a day".

Re: A terrible schema from a clueless programmer

#314

Post by savant_penguin » Sun, Nov 07, 2021, 10:01 PM UTC

I more or less thrown the towel on that "not being clueless anymore"

Sure, I'll try and study to keep myself on my feet, but there is soo much to learn that I'd rather focus on the technologies needed right _now_ to get things done

Way more often than not "thing A has to be done with tech B" and the expectation of getting it done without multiple foot guns in gone

Re: A terrible schema from a clueless programmer

#315

Post by lern_too_spel » Sun, Nov 07, 2021, 10:02 PM UTC

erikerikson wrote: post #156

globular-toast wrote: post #128

Earlier quoted context omitted.

> Clearly it worked. No it didn't. Someone else put an index on the main table and that worked.

The article declared that it was running in production and satisfying the requirement. Do you have a different definition of working? I say that to clarify what I was attempting to communicate by "it worked". I think you're remarking about the relationship between normalized tables and indexes. That relationship does exist in some databases. Please say more if I'm missing your point.

GP's point is that the final schema has a primary key index on the main table that solved the problem, and then it has a bunch of useless additional tables. The solution was just to add a primary key index on the main table. Adding the additional tables just slows down the query because it now has to do five index lookups instead of one.

Re: A terrible schema from a clueless programmer

#316

Post by mixmastamyk » Sun, Nov 07, 2021, 10:04 PM UTC

soheil wrote: post #243

ryukafalz wrote: post #171

Earlier quoted context omitted.

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.

dbeaver CE is free.

Re: A terrible schema from a clueless programmer

#317

Post by noduerme » Sun, Nov 07, 2021, 10:04 PM UTC

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…

>>> Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table.

How do you mean? If id1 is unique on table A, and table B has a foreign key dependency on A.id, then yeah you still have id1 in twenty locations but it's normalized in that altering the referenced table once will alter the joined value in all twenty cases.

This might not be important in the spam-graylisting use case, and very narrowly it might be 3NF as originally written, but it certainly wouldn't be if there were any other data attached to each email address, such as a weighting value.

Re: A terrible schema from a clueless programmer

#318

Post by Animats » Sun, Nov 07, 2021, 10:04 PM UTC

noisy_boy wrote: post #119

ccleve wrote: post #46

Earlier quoted context omitted.

It's not an index on every column. It's a single index across all columns.

Which will be updated every time there is a new combination.

Which is OK. It's not like the whole index has to be rebuilt. It's cheaper than O(log N).

Re: A terrible schema from a clueless programmer

#319

Post by eklitzke » Sun, Nov 07, 2021, 10:05 PM UTC

pgeorgi wrote: post #272

junon wrote: post #238

Earlier quoted context omitted.

MySQL, at least as far back as 2000, had indexes. http://web.archive.org/web/20020610031610/http://www.mysql.c...

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.

Indexes in MySQL worked fine in 2002, at least for the use case described in this article.

Re: A terrible schema from a clueless programmer

#320

Post by yesenadam » Sun, Nov 07, 2021, 10:08 PM UTC

lamontcg wrote: post #216

egeozcan wrote: post #185

Earlier quoted context omitted.

I feel like that phrase has been there forever, and I'm exactly 35. Will this happen more often as I grow older? Ugh. Feels weird. Maybe also a bit depressing.

It was weird seeing all the spongebob squarepants memes take over the internet when I was too old to ever grow up with that. I turned 28 in 1999 when that first aired. That was my "holy shit I'm so old" moment when that finally trickled up into my awareness as someone nearly turning 40 or so.

Same age as you. I have a lot of friends in the Philippines. I swear the Facebook employment info of half the people in the Philippines says they work at the Krusty Krab. (And for most filipinos, the internet = facebook.) For some reason I never asked what that meant, and for many years I thought that was just some odd joke, and was vaguely puzzled about how widespread it is. Eventually I happened on the Spongebob connection!