A terrible schema from a clueless programmer • Hacker News Board

Re: A terrible schema from a clueless programmer

#301

Post by noduerme » Sun, Nov 07, 2021, 9:39 PM UTC

dasil003 wrote: post #194

otterley wrote: post #152

Earlier quoted context omitted.

I’d be a lot more sympathetic if the major RDMSes didn’t have outstanding and thorough reference manuals or that there weren’t a mountain of books on the subject that cover, among other things, the topic of indexing and its importance. MySQL’s manual, for example, has covered this subject from the very beginning: https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html (I don’t have the 3.x manuals handy but it was…

I couldn't disagree more with this comment. No amount of reading documentation teaches you how to build production systems. You progress much faster by getting your hands dirty, making mistakes, and learning from them. The challenge of writing good software is not about knowing and focusing on the perfection every gory detail, it's about developing the judgement to focus on the details that actually matter. Junior en…

But it's also about constantly asking yourself "how can this be done better? What do I not know that could improve it?"

I learned probably 80% of what I know about DB optimization from reading the Percona blog and S.O. The other 20% came from splashing around and making mistakes, and testing tons of different things with EXPLAIN. That's basically learning in a vacuum.

Re: A terrible schema from a clueless programmer

#302

Post by isoskeles » Sun, Nov 07, 2021, 9:43 PM UTC

crispyambulance wrote: post #287

isoskeles wrote: post #255

Earlier quoted context omitted.

I couldn't disagree more with this comment. No amount of getting your hands dirty and making mistakes teaches you how to learn from your mistakes, nor changes the impact of the mistakes. You also progress much faster by learning from other peoples' mistakes, this is why written language is so powerful. Honestly, I think your comment is okay minus the fact that you're trying to highlight such hard disagreement with a…

> No amount of getting your hands dirty and making mistakes teaches you how to learn from your mistakes... Categorically wrong. Mistakes, failure, and trial and error are very much a part of developing skills. If you're not making mistakes, you're also not taking enough risks and thus missing out on opportunities for growth.

Yes, mistakes are required to learn from them, but making mistakes does not imply that a person will learn from their mistakes.

Re: A terrible schema from a clueless programmer

#303

Post by misnome » Sun, Nov 07, 2021, 9:44 PM UTC

sseagull wrote: post #203

wwweston wrote: post #180

Earlier quoted context omitted.

Old enough some people under 35 may not have encountered it… ;)

Well, then, they are part of the 10,000 https://xkcd.com/1053/

Only if they were born in the US

Re: A terrible schema from a clueless programmer

#304

Post by mixmastamyk » Sun, Nov 07, 2021, 9:44 PM UTC

chrischapman wrote: post #49

I think the 'terrible schema' thing is a secondary issue. The important take away for me was this: > Considering that we as an industry tend to chase off anyone who makes it to the age of 35, is it any surprise that we have a giant flock of people roaming around trying anything that'll work?

Unfortunately it is the penultimate sentence in a sizable post.

Re: A terrible schema from a clueless programmer

#305

Post by HahaReally » Sun, Nov 07, 2021, 9:46 PM 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.

OR we could demand they get expensive irrelevant degrees and be geniuses at coding by the time they graduate with no relevant skills! Hey, in fact, let’s just do that instead. :)

Re: A terrible schema from a clueless programmer

#306

Post by nlitened » Sun, Nov 07, 2021, 9:48 PM UTC

lern_too_spel wrote: post #292

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…

If the amount charged isn't proportional to the work done, the cloud provider would quickly go out of business.

My VPS provider gives me unlimited traffic, unlimited disk reads, and unlimited DB rows touched for fixed 30 dollars a month, regardless of how much CPU and RAM I keep loaded.

This makes me think that these things are at least 100x cheaper than AWS might want to make me believe.

Re: A terrible schema from a clueless programmer

#307

Post by noduerme » Sun, Nov 07, 2021, 9:49 PM UTC

ericbarrett wrote: post #245

junon wrote: post #34

Earlier quoted context omitted.

Indexing existed 15 years ago. The article never mentions why indexing didn't solve this problem. Super weird take on the author's part...

Early InnoDB* had pretty strict limits on varchar indexes and was not the most efficient. I don't remember the details but it's entirely possible the single-table format Rachel described ran head on into those limitations. Also remember indexes take space, and if you index all your text columns you'll balloon your DB size; and this was 2002, when that mattered a lot more even for text. Indexes also add write and comp…

To me it seems that spreading it over four tables would lead to a lot more potential read locks while the big combined table is waiting for a join on each of the others, and some process is trying to insert on the others and link them to the main. This is assuming they were using foreign keys and the main table 4-column index was unique.

Re: A terrible schema from a clueless programmer

#308

Post by IceDane » Sun, Nov 07, 2021, 9:49 PM UTC

enraged_camel wrote: post #248

IceDane wrote: post #47

The moral of this post really falls flat coming from this author, most of whose posts are snarky, passive-aggressive rants where she rants about someone's alleged incompetence or something similar.

This is a really weird ad hominem that is unrelated to the post itself.

Ad hominem?

It seems pretty relevant to me that the author mostly writes snarky articles which directly contradict the moral of this story.

For the record, I totally agree with the moral of the story. But we can't just blindly ignore context, and in this case the context is that the author regularly writes articles where someone else is ridiculed because the author has deemed them incompetent.

In a similar vein, no one would just ignore it and praise Zuckerberg if he started discussing the importance of personal privacy on his personal blog.

Re: A terrible schema from a clueless programmer

#309

Post by lern_too_spel » Sun, Nov 07, 2021, 9:53 PM UTC

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…

They are not significantly different. It just means sorting using more than one column.

Re: A terrible schema from a clueless programmer

#310

Post by mixmastamyk » Sun, Nov 07, 2021, 9:54 PM UTC

jeroenhd wrote: post #94

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…

Based on this piece of old documentation I found [0] for MySQL 3.23 (the most recent version in 2002 as far as I can tell), certain types of indices were only available on certain types of engines. Furthermore, columns were restricted to 255 characters, which may be too short for some of the fields saved in the database. Modern databases abstract away a lot of database complexity for things like indices. It's true th…

> All that doesn't mean it was probably a terrible design back when it was written. We're talking database tech of two decades ago, when XP had just come out and was considered a memory hog because it required 128MB of RAM to work well.

A lot of this stuff was invented in the 70s, and was quite sophisticated by 2000. It just wasn't free, rather quite expensive. MySQL was pretty braindead at the time, and my recollection is that even postgres was not that hot either. We've very lucky they've come so far.