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

161–170 of 493 posts

Re: A terrible schema from a clueless programmer

#161

Post by darkwater »

hn_throwaway_99 wrote: post #131
darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

The reason "the details are important" here are not because of the nitty gritty around what mistakes a "novice" programmer made. They are important because the present incarnation of the author is making all the wrong diagnoses about the problems with the original implementation, despite doing it with an air of "Yes, younger me was so naive and inexperienced, and present me is savvy and wise".

She explained the problem, the first not working solution and the second working solution as they really happened in 2002 as an example. The real point is the last part of the post. And it was not "how to properly implement an sql based filter for open proxies in your MTA".

Re: A terrible schema from a clueless programmer

#162

Post by pushrax »

jugg1es wrote: post #150
pushrax wrote: post #130

Earlier quoted context omitted.

This entirely depends on what kind of index is used. A sorted index, such as a B or B+ tree (used in many SQL databases), will allow for fast point/range lookups in a continuous value space. A typical inverted index or hash based index only allows point lookups of specific values in a discrete value space.

It was a sorted BTREE index in MySQL 5.x. I agree that its supposed to be fast but it just wasn't for some reason.

Are you sure it was actually using the index you expected? There are subtleties in index field order that can prevent the query optimizer from using an index that you might think it should be using.

One common misstep is having a table with columns like (id, user, date), with an index on (user, id) and on (date, id), then issuing a query like "... WHERE user = 1 and date > ...". There is no optimal index for that query, so the optimizer will have to guess which one is better, or try to intersect them. In this example, it might use only the (user, id) index, and scan the date for all records with that user. A better index for this query would be (user, date, id).

Re: A terrible schema from a clueless programmer

#163

Post by rmk »

darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

I think you are leaping to conclusions here. It is possible that most people want to keep HN focused on technical conversations and use this as an opportunity to learn something (I certainly do), which is why you are seeing many more comments on the technical aspects.

Re: A terrible schema from a clueless programmer

#164

Post by [deleted] »

darkwater wrote: post #48

I'd really love to be snarky here but I'll try to be polite: all those comments about the example situation are missing the whole point of the post. And it really worries me that there is a good chunk of the tech workers that just ignores the real meaning of something and just nitpick about stupid implementation details. The post is about managing rookie errors, being empathetic and also warn the ageism that pervades…

[deleted]

Re: A terrible schema from a clueless programmer

#165

Post by drchickensalad »

jugg1es wrote: post #118

One of the most useful lessons I ever learned about designing database schemas was the utter uselessness of indexing and searching on datetime fields. Since virtually every value is going to be different, indexing and searching on that field is (almost) no better than having no index on the datetime field at all. It was a revelation to me when I decided to experiment with having a indexed date-ONLY field and using th…

I have no idea how you came to this conclusion, but indices on datetime fields are completely required to bring down the seek time from O(n) to O(log(N)), plus the length of the range. Massive parts of the businesses I've worked at would be simply impossible without this.

The cardinality of the index has other ramifications that aren't generally important here.

Re: A terrible schema from a clueless programmer

#166

Post by lamontcg »

carapace wrote: post #56
welder wrote: post #9

The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…

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

> It seems a lot of folks are getting nerd-sniped

that is an excellent term.

Re: A terrible schema from a clueless programmer

#167

Post by dehrmann »

mewpmewp2 wrote: post #78
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 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…

Yeah, I'm not clear on how multiple tables fixed it other than allowing you to scan through the main table faster.

Multiple tables could be a big win if long email addresses are causing you a data size problem, but for this use case, I think a hash of the email would suffice.

Re: A terrible schema from a clueless programmer

#168

Post by jacobolus »

lamontcg wrote: post #166
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.

> It seems a lot of folks are getting nerd-sniped that is an excellent term.

Origin: https://xkcd.com/356/

Re: A terrible schema from a clueless programmer

#169

Post by unclebucknasty »

welder wrote: post #9

The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…

What's funny is there seems to be a lot of debate among grizzled veterans here about whether her old solution is actually better than the new. We're getting into index types, engine implementations, normal forms, etc. And really, this is a relatively simple development problem that I can easily imagine on an interview test.

Now, imagine a new engineer, just getting started trying to make sense of it all, yet being met with harsh criticism and impatience.

Maybe that was exactly her point--to get everyone debating over such a relatively trivial problem to communicate the messages: Good engineering is hard. Everyone is learning. Opinions can vary. Show some humility and grace.

Post reply on HN