A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#401

Post by sigstoat » Mon, Nov 08, 2021, 3:52 AM UTC

ipaddr wrote: post #102

adfgaertyqer wrote: post #72

Earlier quoted context omitted.

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.

> Adding an index now increases the insert operation cost/time

that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert".

the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.

Re: A terrible schema from a clueless programmer

#402

Post by [deleted] » Mon, Nov 08, 2021, 3:56 AM UTC

mwcampbell wrote: post #270

twic wrote: post #217

Earlier quoted context omitted.

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.

If even someone with Rachel's level of experience still doesn't know all the minutiae of database optimization, I think that just amplifies her point about the importance of mentoring novices.

[deleted]

Re: A terrible schema from a clueless programmer

#403

Post by thaumasiotes » Mon, Nov 08, 2021, 4:02 AM UTC

ncmncm wrote: post #388

thaumasiotes wrote: post #372

Earlier quoted context omitted.

> authk I've always seen authn. Where'd you pick this usage up?

You need to distinguish authentication from authorization. "auth9n" and "auth8n" might be a slight bit too obsure for the purpose.

What a completely nonresponsive "response". To the best of my knowledge, no one has ever even tried to use auth9n or auth8n. But that's not where the 'n' in authn comes from. authn and authk are both equally distinct from authz.

Re: A terrible schema from a clueless programmer

#404

Post by philliphaydon » Mon, Nov 08, 2021, 4:09 AM UTC

wvenable wrote: post #197

philliphaydon wrote: post #19

Earlier quoted context omitted.

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.

Using space to avoid joins will not necessarily improve performance in an RDBMS -- it might even make it worse.

The inverse of your statement is also true. Denormalizing the database to avoid duplicating data will not necessarily improve performance in an RDBMS - it might even make it worse.

Re: A terrible schema from a clueless programmer

#405

Post by _carbyau_ » Mon, Nov 08, 2021, 4:13 AM 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.

For me it is Pokemon. I was a few years older than the demographic it targeted.

I could have jumped on it and played it but I figured just another toy craze, it'll be over before long.

Re: A terrible schema from a clueless programmer

#406

Post by eyelidlessness » Mon, Nov 08, 2021, 4:24 AM UTC

AdrianB1 wrote: post #375

superjan wrote: post #326

Earlier quoted context omitted.

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

That sounds like newbie developers wrote that and no DBAs were involved in checking the code, not a database design problem, you are throwing blame without knowing enough about the matter. If your string fit in a varchar, it makes absolutely no sense to change it to nvarchar because your query has unicode as input.

I reread the parent quote several times and cannot detect a hint of throwing blame at all.

Re: A terrible schema from a clueless programmer

#407

Post by xupybd » Mon, Nov 08, 2021, 4:29 AM UTC

AdrianB1 wrote: post #375

superjan wrote: post #326

Earlier quoted context omitted.

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

That sounds like newbie developers wrote that and no DBAs were involved in checking the code, not a database design problem, you are throwing blame without knowing enough about the matter. If your string fit in a varchar, it makes absolutely no sense to change it to nvarchar because your query has unicode as input.

What? The database made the call to compare the strings as Unicode. Are you accusing the DB developers of not doing their job?

Re: A terrible schema from a clueless programmer

#408

Post by eyelidlessness » Mon, Nov 08, 2021, 4:37 AM UTC

gonzo41 wrote: post #391

thaumasiotes wrote: post #372

Earlier quoted context omitted.

> authk I've always seen authn. Where'd you pick this usage up?

good question! I meant authn. I think I just subbed in the phonic from the ..cation part of the word.

You get my upvote both for having a friendly response, and because your answer is what my brain filled in and I’m glad I don’t need to learn a new shorthand.

Re: A terrible schema from a clueless programmer

#409

Post by invincivlepvt » Mon, Nov 08, 2021, 4:40 AM UTC

https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/social-media-marketing-jalandhar/ https://invincivlepvt.com/email-marketing-jalandhar/ https://invincivlepvt.com/social-media-optimisation-jalandha... https://invincivlepvt.com/sms-marketing-jalandhar-2/

Re: A terrible schema from a clueless programmer

#410

Post by rukuu001 » Mon, Nov 08, 2021, 4:44 AM UTC

A story of how a junior dev becomes a little less junior.

The original implementation was the 'obvious' one, and not terrible.

Then experience of the live system + MySQL demonstrated problems with that.

So a non-obvious but better-performing solution was implemented instead.

Great! Junior dev walks away with a mistrust of both long repeated strings appearing in a DB table, AND a mistrust of database internals. A fine lesson indeed.

(And remember, this was the era of the all-powerful DBA who could ruthlessly de-normalise your schema at a moments notice to improve performance).