32 ms·

A terrible schema from a clueless programmer

• ed25519FUUU 5y ago
  > id | ip | helo | m_from | m_to | time | ... I'm not a database person, but this doesn't seem too bad if there was at least an index on IP. The performance there would probably be good enough for a long time depending on the usage. But is 6 tables (indexed on the value) really better than 1 table with 6 indexes?
• cat199 5y ago
  orr.. could have switched to https://man.openbsd.org/spamd https://man.openbsd.org/spamd greylisting a few months later in 2003
• nlitened 5y ago
  > 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 mentions it?
  • lern_too_spel 5y ago
    If the amount charged isn't proportional to the work done, the cloud provider would quickly go out of business.
    • nlitened 5y ago
      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.
      • lern_too_spel 5y ago
        On a single instance. The more instances you use, the more you will be charged. This service handles automatic scalability and resilience with multiple instances for you, allowing it to charge less than $30.
  • ricardobeat 5y ago
    You'll probably enjoy reading this article that was on the front page yesterday: https://briananglin.me/posts/spending-5k-to-learn-how-database-indexes-work/ https://briananglin.me/posts/spending-5k-to-learn-how-databa...
    • notreallyserio 5y ago
      Nice. I was thinking of an earlier post involving cloud storage buckets with no index.
    • nlitened 5y ago
      Thank you. I cannot say I enjoyed it, I am distressed now. Using a “planet scale” “serverless” database to store just 750k download records per day, and paying per DB row touched, and “thank you for letting us off the hook this time”. This reads like satire.
  • deleted 5y ago
    [deleted]
• tyingq 5y ago
  Feels like you could just concatenate and hash the 4 values with MD5 and store the hash and time. Edit: I guess concatenate with a delimiter if you're worried about false positives with the concat. But it does read like a cache of "I've seen this before". Doing it this way would be compact and indexed well. MD5 was fast in 2002, and you could just use a CRC instead if it weren't. I suppose you lose some operational visibility to what's going on.
  • ok123456 5y ago
    For the 2021 version, you'd just generate a bloom filter/cuckoo hash from all the data gathered by your spamhaus database periodically. Make a separate one for each value in your tuple and your score would be the number of the sub-hashes that matched.
    • ummonk 5y ago
      “Periodically” here would have to be quite frequent, as you have to rebuild the database before the next retry.
      • ok123456 5y ago
        A couple times a day at most?
        • ummonk 5y ago
          From the post, you'd have to be rebuilding it every 15 minutes: > A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through.
          • ok123456 5y ago
            Still better than locking the tables every time you receive a piece of mail.
  • 5y ago
• gadrev 5y ago
  What I got from this post is a headache after reviewing the wikipedia entries on 3NF (all all of it's dependencies: 1NF, 2NF, also 3.5NF). Man the language is dense. j/k, always nice to review some of this "basic" stuff (that you barely use in everyday work but it's good to have it in the back of your head when changing something in the db schema)
• chrischapman 5y ago
  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?
  • alisonkisk 5y ago
    That canard isn't true.
  • mixmastamyk 5y ago
    Unfortunately it is the penultimate sentence in a sizable post.
  • ummonk 5y ago
    Maybe I’ll change my mind in 5 years, but I have a hard time believing engineers over the age of 35 get chased off. What actually seems to be the case is that the field skews young because it’s rapidly growing, and older engineers choose to retire early because they can afford to. Which results in the same problems but the root cause is different.
• biglost 5y ago
  Or store the ip address as int64 with index, or better both solutions together
• deleted 5y ago
  [deleted]
• fortran77 5y ago
  The "NoSQL" movement -- which I wisely rejected -- is also responsible for this.
  • harel 5y ago
    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.
    • tester756 5y ago
      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?
      • harel 5y ago
        I don't think there is (or should be) a "default". Default selections suggest they are picked "just because". There is a problem/use-case and a tool to solve it. Sometimes it's relational, sometimes it's nosql, sometimes it's both or even none...
  • jugg1es 5y ago
    There are problems that nosql databases solve easily that require a lot of fancy tricks in a RDBMS.
  • ummonk 5y ago
    Huh? I strongly prefer relational databases for most things but this particular example is actually begging for a simple key-value store.
• tomsmeding 5y ago
  Why exactly would it be so bad to just put a suitable index on the table containing strings? The time complexity of the resulting search would be the same, so I assume there will be some constant factor slowdowns. Is it that indices over string fields are stored inefficiently on disk? (If so, can that not be fixed in the db engine directly?) Or is this fine today but wasn't fine 15 years ago?
  • thehappypm 5y ago
    At the worst you could concatenate the strings and have a single primary key.. or hash the string concatenation.
  • jerjerjer 5y ago
    Yeah, while this is not optimal (ip should be converted into integer, time should be ts), the table would be small (as old entries could be safely deleted). The only real issue is the lack of indices. Also is helo field even needed?
    • mjevans 5y ago
      Yes. A proper mail implementation won't randomize it, so it should be consistent. Depending on the design of the validation stages it might only need a case-normalized lookup to the string index table and that id as part of the final multi-column unique index, and in that last case only to convey to the database the expectations to optimize around (and require the data adhere to).
    • ttfkam 5y ago
      IP should NOT be an integer. IPv6 does not fit in a database integer. MySQL has functions to convert to bytes. Even better, use a database with a proper inet datatype. That way you get correctness, space efficiency, and ability to intelligently index.
      • _3u10 5y ago
        In 2002 it definitely should have been an integer. In 2021 I’d recommend just turning off the IPv6 allocation or deleting it from DNS like this site does.
• marginalia_nu 5y ago
  https://news.ycombinator.com/item?id=29137141 https://news.ycombinator.com/item?id=29137141
  • dang 5y ago
    Oops - missed that one. I guess there was more appetite for discussion.
• webstrand 5y ago
  While the normalized version is more compact and doesn't store redundant data, it _also_ needs an index on the four columns or it'll have to check every row in the table. A similar index added to the original denormalized table would have given comparable query performance. The table schema isn't terrible, it's just not great. A good first-pass to be optimized when it's discovered to be overly large.
  • deleted 5y ago
    [deleted]
  • deleted 5y ago
    [deleted]
  • Closi 5y ago
    > The table schema isn't terrible, it's just not great. It depends - if this was the full use-case then maybe a single table is actually a pretty good solution.
  • rk06 5y ago
    that is not true. the sub tables guarantee that value is unique in the entire column. while main table did not have any such guarantees, so, there will be a lot of indexing and duplication in atleast 3 (of 4 sub tables) Moreover, indexing would be much faster and efficient as there are only unique values in sub tables
    • int_19h 5y ago
      Why wouldn't it have such guarantees, if the indices in question are defined as UNIQUE?
      • rk06 5y ago
        Because they are not unique in main table. For eg: if there are multiple emails from same domain name, then domain name column will have same entry multiple times. When domain is moved to sub table, main table still has duplicate entries, in form of foreign keys, while domain table has unique entry for each domain name
• deleted 5y ago
  [deleted]
• ccleve 5y ago
  Sorry, no. The original schema was correct, and the new one is a mistake. The reason is that the new schema adds a great deal of needless complexity, requires the overhead of foreign keys, and makes it a hassle to change things later. It's better to stick the the original design and add a unique index with key prefix compression, which all major databases do these days. This means that the leading values gets compressed out and the resulting index will be no larger and no slower than the one with foreign keys. If you include all of the keys in the index, then it will be a covering index and all queries will hit the index only, and not the heap table.
  • kasey_junk 5y ago
    She doesn’t mention the write characteristics of the system but she implies that it was pretty write heavy. In that case it’s not obvious to me that putting a key prefix index on every column is the correct thing to do, because that will get toilsome very quick in high write loads. Given that she herself wrote the before and after systems 20 years ago and that the story was more about everyone having dumb mistakes when they are inexperienced perhaps we should assume the best about her second design?
    • ccleve 5y ago
      It's not an index on every column. It's a single index across all columns.
      • noisy_boy 5y ago
        Which will be updated every time there is a new combination.
        • Animats 5y ago
          Which is OK. It's not like the whole index has to be rebuilt. It's cheaper than O(log N).
  • msqlqthrowaway 5y ago
• welder 5y ago
  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: EVERYONE goes through this, particularly if operating in a vacuum with no mentorship, guidance, or reference points. 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?
  • Aeolun 5y ago
    > My point is: EVERYONE goes through this A lot of the basic issues beginners go through can be mitigated by paying attention in class (and/or having formal education in the first place). I’ll grant that there are other, similar, things that everyone will go through though. My own was manually writing an ‘implode’ function in Javascript because I didn’t know ‘join’ existed.
  • carapace 5y ago
    (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.
    • twic 5y ago
      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.
      • emilsedgh 5y ago
        > but your example is of a case where you did something fine but then replaced it with something worse They way I see it is that it was a case of something that wasn't working, then replaced with something that was working. I don't know what your metrics about "good" or "bad" are but eventually they got a solution that covered their use cases and the solution was good enough for stakeholders and that is "good".
• hn_throwaway_99 5y ago
  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 if a more normalized DB structure would be warranted, but from her description I would say it wasn't.
  • gfodor 5y ago
    The point is she was being hyper critical of her past schema to make a larger point.
    • mwcampbell 5y ago
      And IIUC, the larger point, made by the twist ending, is that we shouldn't be so critical, but try to actually help newbies learn what we insist that they should know.
      • erikerikson 5y ago
        It seemed to me the point was that if the industry remains designed to push out experienced engineers that there won't be anyone to mentor the engineers that are just beginning their careers. Further that we will bend the productivity of the field downward and have less effective systems.
        • sokoloff 5y ago
          In what way is the industry designed that way now? Maybe some get sucked into management roles, but I don’t see any evidence of a broad design to push them out.
          • wbl 5y ago
            Look at the way we screen resumes, or valorize work conditions incompatible with having family obligations. Ageism in the tech industry is rife.
• globular-toast 5y ago
  > That's right, I was that clueless newbie Questionable use of past tense here.
  • mwcampbell 5y ago
    Why? She's clearly not a clueless newbie now.
    • framecowbird 5y ago
      I think the OP was sarcastically implying that she still is.
      • inopinatus 5y ago
        Such remarks generally backfire onto the author, as indeed in this case.
    • kofejnik 5y ago
      Right, not a newbie anymore
• Grimm1 5y ago
  Heh at the end I think I may have been called out for my harsh response to the post being referenced at the start here on HN since it seemed to raise some hackles. Borrowing from other social media terminology here, "subtweeted" but by a blog post, not sure how I feel about that. Honestly it almost makes me feel compelled to start writing more for the industry but to be honest, my opinion is most ideas are not great, or not novel, up to and including my own. So writing about them seems likely not to be beneficial. I guess you could argue I could let the industry be the judge of that though.
• junon 5y ago
  Err. Wat. The original schema was (mostly) fine. It had no indexes. The second schema looks like it tried to work around lack of database optimization features. It's in no way "better" from a data design standpoint. A database with good string index support isn't doing string comparisons to find selection candidates - at least, not initially. What a bizarrely confident article.
  • pgeorgi 5y ago
    > lack of database optimization features That's a perfection description of mysql as of 2002.
    • junon 5y ago
      MySQL, at least as far back as 2000, had indexes. http://web.archive.org/web/20020610031610/http://www.mysql.com/documentation/mysql/full/ http://web.archive.org/web/20020610031610/http://www.mysql.c...
      • pgeorgi 5y ago
        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.
        • junon 5y ago
          That's pretty beside the point though, the concept of indexing had existed for decades even prior to that.
          • pgeorgi 5y ago
            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.
• dorianmariefr 5y ago
  id | ip_address | from | to | content | updated_at | created_at And indexes where you need them. Normalize data like IP addresses, from and to. And you are good to go.
• inopinatus 5y ago
  The stench of *splaining in these remarks is nigh overwhelming, and mostly missing the point to boot.
• stickfigure 5y ago
  Assuming this needs to be optimized for massive scale, just hash the values to a single indexed field. And use something other than an RDBMS. Put the hash in Redis and expire the key; your code simply does an existence check for the hash. You could probably handle gmail with a big enough cluster. That super-normalized schema looks terrible.
  • kodt 5y ago
    I too recommend using Redis in 2002.
    • stickfigure 5y ago
      Fair enough, but I think the issue here is recognizing that the underlying business operation is a hit test on an expiring hash key. You could have used MySQL as a key/value store or looked for something more specialized.
    • ummonk 5y ago
      “Hash it and store in BerkeleyDB” would have been a ridiculously easy solution at the time.
  • teen 5y ago
    ++ correct answer here
• IceDane 5y ago
  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.
  • enraged_camel 5y ago
    This is a really weird ad hominem that is unrelated to the post itself.
    • IceDane 5y ago
      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.
    • Nebasuke 5y ago
      I have to agree with the OC. I read this post, the reply post (https://rachelbythebay.com/w/2021/11/07/select/ https://rachelbythebay.com/w/2021/11/07/select/) and "The ONE" (https://rachelbythebay.com/w/2018/04/28/meta/ https://rachelbythebay.com/w/2018/04/28/meta/) post and I too was struck by the passive aggressive/defensive tone. To me it really clashed with the original post which concludes we need guidance/mentorship/reference points. Most of the comments in Hackernews are constructive or genuinely curious, don't just dismiss the author and instead provide things to learn. For the author to then write a reply and dismiss all these comments as comments that are really saying "I am THE ONE. You only need to know me. I am better than all of you." is just plain rude and completely counter to their own point.
  • mwcampbell 5y ago
    The ending: > It's a massive problem, and we're all partly responsible. I'm trying to take a bite out of it now by writing stuff like this. suggests to me that maybe she has recently had a change of heart about such rants. I'm willing to give her the benefit of the doubt, because we all need to give each other the benefit of the doubt these days.
• darkwater 5y ago
  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 sector. TBH about this last point IDK the situation nowadays in Silicon Valley, but in Europe my limited experience is that ageism is not that bad; kt's actually difficult to find seasoned developers. Edit: typos
  • deleted 5y ago
    [deleted]
  • Cpoll 5y ago
    I don't buy it. If the only point was managing rookie errors, etc., the blog post shouldn't have been 15~ paragraphs of technical discussion and 2 paragraphs at the bottom of "my point is ..." You can't advocate that people just ignore 80% of the article because they're "not the point of the post." I'm sure a good chunk of tech workers are worried that when an influential blogger writes something like this a couple hundred developers will Google 3NF and start opening PRs.
    • deleted 5y ago
      [deleted]
    • unethical_ban 5y ago
      It's a narrative. A story. She told it in that way to lure people who get hooked on technical discussion, and then make the point that they probably would have missed or ignored. Without a narrative example, she could have just said "I made mistakes too, live and learn" but she chose to provide an example before presenting the thesis. And hey, what's so bad about people learning about 3NF? Are you not supposed to know what that is until you're some mythical ninth level DBA?
      • Cpoll 5y ago
• havocsupreme 5y ago
  I’m pretty sure all the people who are criticizing the database design haven’t read the ending. The article isn’t about the schema, it’s about helping those entry level programmers
  • loeg 5y ago
    It would be more effective at making that point if it didn’t have the confused and arguably incorrect section in the middle. If as a writer, the audience misses your point, maybe it’s a problem with your writing rather than the audience.
  • empthought 5y ago
    You don’t think it’s helpful for people to steer entry level programmers away from bad ideas like this one?
  • ummonk 5y ago
    The problem is that the technical narrative in the post contributes to imposter syndrome and gaslights entry level programmers into thinking they’re doing database design wrong.
• lifeisstillgood 5y ago
  The one thing I would add here is that the devlead (or equivalent - there always is someone) should have added at least one test. Not a unit test or 99% co drags test but a "does it do what we want" test - a golden path test. I came across something like this with an apprentice recently. I let the lack of a repeatable test pass because speed / time / crunch etc and the obvious how can that possibly fail code of course failed. The most basic test would have caught it in the same way some basic performance testing would have helped rachel however many years back. It's hard this stuff :-)
• kofejnik 5y ago
  IMO she’s still pretty clueless (sorry!), but over the years got better at self-promotion So the real lesson is to be very careful as to who you listen to
  • dang 5y ago
    Personal attacks will get you banned here. Please review https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html and don't do any more of this on HN. We detached this subthread from https://news.ycombinator.com/item?id=29140272 https://news.ycombinator.com/item?id=29140272.
  • crispyambulance 5y ago
    She has never been "clueless" and has written insightfully for years.
    • globular-toast 5y ago
      Well she's clueless about what actually sped up this database query.
      • yuliyp 5y ago
        Without knowing which database engine (MySQL comes with more than one) she was using, nor the testing that she actually performed, what makes you say that?
        • mst 5y ago
          I remember versions of mysql whose string indexing was sufficiently limited on every available backend that this would've been the correct change. Hell, back in ... 2001? I think? I ended up making exactly the same set of changes to an early database design of mine, for pretty much the same stated reasons and with very similarly pleasant effects on its performance.
      • Lammy 5y ago
• kofejnik 5y ago
  The initial design was fine and btw it did not violate 3NF, actually (if you add auto increment pk). A single composite index would’ve most likely solved all problems
• cuu508 5y ago
  > The observation was that we could probably store the IP address, HELO string, FROM address and TO address in a table, and send back a 4xx "temporary failure" error the first time we saw that particular tuple (or "quad"). A real mail server which did SMTP properly would retry at some point, typically 15 minutes to an hour later. If it did retry and enough time had elapsed, we would allow it through. I've run into this form of greylisting, it's quite annoying. My service sends one-time login links and authorization codes that expire in 15 minutes. If the email gets delayed, the user can just try again, right? Except I'm using AWS SES, so the next email may very well come from a different address and will get delayed again.
  • loeg 5y ago
    You could have your codes expire after an hour or day instead?
    • cuu508 5y ago
      That's a solution, but with tradeoffs: * security, an attacker has more time to intercept and use the links and codes. * UX, making the user wait 15+ minutes to do certain actions is quite terrible. I've had a couple support requests about this. The common theme seems to be the customer is using Mimecast, and the fix is to add my sender address in a whitelist somewhere in their Mimecast configuration.
  • merb 5y ago
    btw. ses now offers unique sending ips
• matsemann 5y ago
  > The first time you encounter something, you're probably going to make some mistakes. There's a post going around tonight about how someone forgot to put an index on some database thing and wound up doing full table scans (or something like that). Which post is referenced here? I also believe this to be a tooling issue. It's often opaque what ends up being run after I've done something in some framework (java jpa, django queries whatever). How many queries (is it n+1 issues at bay?), how the queries will behave etc. Locally with little data everything is fine, until it blows up in production. But you may not even notice it blowing up in production, because that relies on someone having instrumented the db and push logs+alarms somewhere. So it's easy to remain clueless.
  • claytonjy 5y ago
    Pretty sure it's this one https://news.ycombinator.com/item?id=29132572 https://news.ycombinator.com/item?id=29132572
• Donckele 5y ago
  Sorry Rachel but the best solution was a simple fix as mentioned by most people here - columns having the right types and indexes. When I was still wet behind the ears I was doing what you are now proposing - but its overkill.
• deleted 5y ago
  [deleted]
• rektide 5y ago
  The popular advice in these comments to use indexes sounds really good. I haven't thought it out fully, but my first thought was "this sounds like a DIY columnar store". A columnar database does a lot of this work for you. I'm not 100% it's a good option in this case, because things like email subjects are not good columnar data. I would like to know more about how problematic that is.
• zephrx1111 5y ago
  I think this post showed us how an engineer on the Mt. stupid looks like. Apparently this design is wrong. We should rely on the indexing ability of database itself, instead of inventing our own indexing system.
• shusson 5y ago
  I would add a counter point to foreign key constraints, they make deletions and updates a lot more costly [1]. [1] https://shusson.info/post/postgres-experiments-deleting-tables-with-referential-integrity https://shusson.info/post/postgres-experiments-deleting-tabl...
• throwawayFanta 5y ago
  I think this blog fails to take into account that 2021 is not 2002. Computer Science is a much more formal/mainstream field of study now, and people don't operate "in a vacuum with no mentorship, guidance, or reference points.” Some comments on the previous blog post raised important questions regarding minimum understanding/knowledge of technology one utilizes as part of their day job. And I would agree that indexing is a fundamental aspect while using relational databases. But unfortunately this isn't very uncommon these days, way too often have I heard people in $bigco say, let's use X, everyone uses X without completely understanding the implications/drawbacks/benefits of that choice.
  • deleted 5y ago
    [deleted]
  • jhgb 5y ago
    I don't see how 2002 was ever different in this from 2021 (except maybe for a stronger international dimension today).
• wdr1 5y ago
  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 were called in. We were pretty puzzled as we had an index and had load tested extensively. We had Oracle support working directly with us & couldn't figure out why queries had started to become table scans. The culprit? A point upgrade to DBD::Oracle (something like X.Y.3 to X.Y.4) introduced subtle but in character sets. So the index required using a particular Unicode character set, and we were specifying it, but when it was translated into the actually query, it wasn't exactly the right one, so the DB assumed it couldn't use the index. Then, when all the banks opened & a large portion of very populous country tried to buy tickets at the same time, things just melted. Not a fun day.
  • fatnoah 5y ago
    I've never had to deal with a production-breaking issue resulting from this, but I have had to debug a collation-related issue in SQL Server that caused table scans to occur. Fortunately, the SQL profiler made it really obvious that a collation mismatch was the culprit.
  • mst 5y ago
    I love DBD::Oracle so much I've always arranged to have other people test oracle related DBIx::Class features for me. Writing the pre-ANSI-JOIN-support SQL dialect code was pretty good fun though.
  • hinkley 5y ago
    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.
    • necovek 5y ago
• lisper 5y ago
  > You know what it should have been? It should have been normalized. No, it should have had a unique index on the first five columns. (Oooh, I just hate it so much when someone gets up on their high horse and opens a blog post with a long condescending preamble about how some blogger got it wrong, and then they get it even wronger.)
• rvr_ 5y ago
  short answer: composite indexes
• jugg1es 5y ago
  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 that to search instead. It improved performance by almost two orders of magnitude. In hindsight, this should have been totally obvious if I had stopped to think about how indexing works. But, as is stated in this article, we have to keep relearning the same lessons. Maybe I should write an article about how useless it is to index a datetime field... EDIT: This was something I ran into about 10 years ago. It is possible there was something else going on at the time that I didn't know about that caused the issue I was seeing. This is an anecdote from my past self. I have not had to use this technique since then, and we were using an on-prem server. It's possible that the rest of the table was not designed well and index I was trying to use was already inefficient for the resources that we had at the time.
  • pushrax 5y ago
    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.
    • jugg1es 5y ago
      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.
      • pushrax 5y ago
        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).
• bizzarojerry 5y ago
  What a bizarre post from an influential blogger so confidently making assertions that are wildly contested even among HN users. If anything this is the problem with industry, articles like this that make vast generalizations from a specific instance, unsubstantiated finger-pointing gestures, and passive aggressively blame the industry as a whole.
• deleted 5y ago
  [deleted]
• fullstackchris 5y ago
  I guess I've come away with a totally different takeaway than most. This post is rather strong on the blame game, which could be fixed by one thing... RTFD! (Read The F**in Docs!) - I only skimmed the post, but its definitely something that would have been avoided had some SQL documentation or introduction been read. I'd argue one of the huge things that differentiates "senior" developers from all the "other" levels - we're not smarter or more more clever than anyone else - we read up on the tools we use, see how they work, read how others have used them before... I understand this was from 2002, but MySQL came out in 1995 - there was certainly at least a handful of books on the topic. Perhaps when just starting off as an intern, you may be able to argue that you are 'operating in a vacuum' but any number of introductory SQL books or documentation could quickly reveal solutions to the problem encountered in the post. (Some of which are suggested in these comments). Of course we all make mistakes in software - I definitely could see myself creating such a schema and forgetting to add any sort of indexing - but when running into performance issues later, the only way you'll be able to know what to do next to fix it is by having read literature about details of the tools you are using. Operating in a vacuum? Then break out of it and inform yourself. Many people have spent many hours creating good documentation and tutorial on many many software tools - use them.
  • fwip 5y ago
    The problem here is "unknown unknowns," coupled with the real business pressure to get solutions out the door. The MySQL manual is (currently) over 6,000 pages. A developer new to SQL or relational DBs doesn't know how many of those are necessary to read and comprehend to deploy a working solution. And in this case, she designed and deployed the system, and it worked and met the business needs for several months. When performance became an issue, she optimized. I'm sure she had plenty of other unrelated work to do in the meantime, especially as a lead/solo dev in the early 2000's. Sounds like a productive developer to me.
• kazinator 5y ago
  > The whole system was reworked to have a table for each set of values. There was now a table of IP addresses, a table of HELO strings, a table of FROM addresses, and a table of TO addresses. Lisp interning, in database tables. A.k.a. Flygweight Pattern.
• rmk 5y ago
  Why not store the IP Address as a 32-bit number (IPv4 addresses)? Why store it as a string in the first place? This is something I did not quite get. Also, wouldn't it be better to split out the domain from the email address for ease of filtering? Also, how does performing joins give a performance advantage here. I'm assuming there would be queries to get at the IDs of at least one, but going up to 4, to get at the IDs of the items in the quad. Then there would be a lookup in the mapping table. I have worked for some time in this industry, but I have never had to deal with relational databases (directly; query tuning and such were typically the domain of expert db people). It would be interesting to see an explanation of this aspect. EDIT: To people who may want to comment, "You missed the point of the article!": no, I did not, but I want to focus on the technical things I can learn from this. I agree that ageism is a serious problem in this industry.
  • pg_bot 5y ago
    You could store the IP as a 32 bit unsigned int. There's no issue with that, but I would probably recommend nowadays to use an internal cidr or inet type if your database supports that. It probably wouldn't be better to split the email address from the domain for filtering since email address formats tend to be a bit tricky. Joins give a performance advantage due to the fact that you aren't duplicating data unnecessarily. The slow query in question becomes five queries (4 for the data once for the final lookup) which can each be done quickly and if any one of them return nil, you can return a timeout.
    • rmk 5y ago
      Yes, some of the databases support native ip address types that can be manipulated efficiently. Better to use that (like inbuilt json querying capabilities in postgres) than come up with your own. It is still not clear how is it better to do up to 5 separate queries or maybe a few joins, than to store the strings and construct indices and such on them? Is the idea that the cost of possibly establishing a new socket connection for some concurrent query execution or sequentially executing 5 queries is still < possible scans of the columns (even with indexing in effect)? Also, even if you had integers, don't you need some sort of an index on integer fields to avoid full table scans anyway?
• Aqueous 5y ago
  There is a time and a place for a denormalized schema. If you are trying to search a large data-set a denormalized schema in a single table with some good indexing is much preferable to joining across 30 tables. Whether you choose normalized or denormalized depends very much on a) what you need to do now b) what you may need to do in the future. Both are considerations.
• mceachen 5y ago
  <pushes glasses up nose> Actually, the Correct Answer is a bloom filter. (And, yes, we had math in the early 2000s.) Snark aside, I'm frustrated for the author. Her completely-reasonable schema wasn't "terrible" (even in archaic MySQL)—it just needed an index. There's always more than one way to do something. It's a folly of the less experienced to think that there's only One Correct Way, and it discourages teammates when there's a threat of labeling a solution as "terrible." Not to say there aren't infinite terrible approaches to any give problem: but the way you guide someone to detect why a give solution may not be optimal, and how you iterate to something better, is how you grow your team.
  • Jolter 5y ago
    To be fair, the “correct way” to do databases at that time was to use third normal form. Putting indices on string columns would have been considered a hack, much like using MySQL was.
    • mst 5y ago
      Plenty of databases at the time had pretty lame string indexing because it simply wasn't something people relied on if they wanted performance, and memory sayeth mysql's were both not particularly efficient and had some nasty technical limitations. On the mysql she was using, breaking things out so it only needed to index ints was almost certainly a much better idea. On anything I'd be deploying to today, I'd start by throwing a compound index at the thing with the expectation that'd probably get me to Good Enough.
    • int_19h 5y ago
      3NF is largely orthogonal to indices on string columns, though. If you need to look something up by string fast, there's no avoiding an index of strings somewhere.
    • jhgb 5y ago
      When has ever been putting indices on string columns "a hack"? If you're looking for that string, which seems to have been the case here in particular, you need that index.
• kpommerenke 5y ago
  While the article is not really about the database schema, could you do a table with just two fields? 1) hash of concat(ip & helo & m_from & m_to) 2) time
• krisgenre 5y ago
  My piano teacher once told me that its common to commit a mistake but its disastrous if you keep practicing without realizing that you have made a mistake. What I look for in a developer is their ability to realize a mistake and find ways to fix it.
• GuB-42 5y ago
  The problem, I think is that most people, me included, don't really know what databases really do. There is a whole lot about optimizing procedural code, with a variety of tools, the dangers of premature optimization and the tradeoff with readability. Anyone with a computer science degree has heard about algorithmic complexity, caches, etc... But databases are just magic. You try things out, usually involving a CREATE INDEX at some point, and sometime it gets faster, so you keep it. Rachel, in he blog post is a good example of that thought process. She used a "best practice", added an index (there is always an index) and it made her queries faster, cool. I don't blame her, it works, and it is a good reminder of the 3NF principle. But work like that on procedural code and I'm sure we will get plenty of reactions like "why no profiler?". Many, many programmers write SQL, but very few seem to know about query plans and the way the underlying data structures work. It almost looks like secret knowledge of the DBA caste or something. I know it is all public knowledge of course, but it is rarely taught, and the little I know about is is all personal curiosity.
  • musingsole 5y ago
    > But databases are just magic. 100%!!! I hate how developers talk about a "database" as a monolithic concept. It's an abstract concept with countless implementations built off of competing philosophies of that abstract concept. SQL is only slightly more concrete, but there's as many variants and special quirks of SQL dialects out there as databases.
  • onionjake 5y ago
    Database indexes are definitely one of the most approachable concepts of databases for computer science majors. If you have studied data structures and Big O notation you have all the concepts needed. While I agree that how a query planner works is one of the most ‘magic’ aspects, I think the output from the query planner in most databases is very approachable as well to regular common programmers and will get you quite far in solving performance issues. We know what full scans are (searching through every element of an array), etc. The challenge is usually discovering that the jargon used in your database really maps to something you do already have a concept about and then reading the database documentation…
• avl999 5y ago
  The responses in this thread are classic pedantic HN with the actual point whoooshing over so many people's head.
  • jeremyjh 5y ago
    I think it actually makes quite a bit of a difference if 20 years later the author still does not understand the actual problem and solution she is using as an example. It actually does undermine her argument that people make mistakes, learn from them and grow etc if the lack of growth is still in evidence 20 years later in the singular example under consideration.
  • deleted 5y ago
    [deleted]
  • bizzarojerry 5y ago
    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.
    • dang 5y ago
      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 https://news.ycombinator.com/newsguidelines.html
• nobodyandproud 5y ago
  If there are millions of people entries coming in per hour, I can see the value. Otherwise, how long is their data retention? Wouldn’t there be more value in cleaning out entries older than, say, two hours?
• ChrisMarshallNY 5y ago
  I'd say that pretty much everyone is a better DB programmer than I am, so I don't really go out of my way to design anything especially robust. I try to design stuff I'm not good at, in a way that makes it easy for someone that knows more than I do, to come in and toss out the work I did, to be replaced with good work. There's lots of stuff that I'm bad at. I'm also good at a fair bit of stuff. I got that way, by being bad at it, making mistakes, asking "dumb" questions, and seeing how others did it.
  • austinjp 5y ago
    > I try to design stuff I'm not good at, in a way that makes it easy for someone that knows more than I do, to come in and toss out the work I did, to be replaced with good work. Sounds familiar. And sometimes that other someone is just a later version of yourself :) I am very grateful when my past self thought to add comments for my future self.
• oconnor663 5y ago
  I think there's an easy "best of both worlds" take here: 1. The majority of the performance problem could've and probably should've been summarized as "you need to use an index". (Maybe there were MySQL limitations that got in the way of indexing back then? But these days there aren't.) 2. Everyone makes mistakes! New programmers make mistakes like not knowing about indexes. Experienced programmers make mistakes like knowing a ton about everything and then teaching things in a weird order. All of these things are ok and normal and part of growing, and it's important that we treat each other kindly in the meantime.
  • mst 5y ago
    Based on my own war stories, if it was an early '00s mysql then I expect "just throw an index at the varchar columns" would've helped but not nearly as much as what she actually did.
• mmaunder 5y ago
  Oh well played!! I was setting up to give the author a hard time about being judgemental, particularly because storing IPs or UUIDs as strings is a mistake I've seen some pretty darn good devs make. Some folks just aren't super strong on schema design and performance but are great at other things. Plus MySQL kind of rocks. Fight me. There are some interesting optimizations that Percona has written about that may improve the performance of OPs schema. The learning never ends. TBH I haven't seen a shortage of mentors. In fact most of our dev team is 35+ and are very approachable and collegial and our internal mentoring programs have been extremely successful - literally creating world experts in their specific field. I don't think we're unique in that respect. I think it's rather unfortunate that the top comment here is a spoiler. Actually reading the post and going on the emotional rollercoaster that the author intended to take you on is the point. Not the punch line.
  • jjice 5y ago
    > Plus MySQL kind of rocks. Agreed. I prefer Postgres for personal projects, but MySQL is a fine database. Honestly, even a relational DB I wouldn't want to use again (DB2...) is still pretty solid to me. The relational model is pretty damn neat and SQL is a pretty solid query language. I wonder how many people disagree with that last part in particular...
    • mmaunder 5y ago
      It’s actually the core of all dev that involves any data storage, today. HN is both aspirational in tech and has a younger demographic so you won’t get any points here for mentioning anything SQL. Hence the downvotes I’m getting. But hey, it’s all good!!
• alksjoflkajsom 5y ago
  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, and from columns, you'd still have no information about the "to" column). Normalization does not mean "the same string can only appear once". Mapping the string "address1@foo.bar" to a new value "id1" has no effect on the relations in your table. Now instead of "address1@foo.bar" in 20 different locations, you have "id1" in 20 different locations. There's been no actual "deduplication", but that's again not the point. Creating the extra tables has no impact on the normalization of the data.
  • deleted 5y ago
    [deleted]
  • twic 5y ago
    This is very slightly not quite true, because the HELO string and the remote address should go hand-in-hand.
    • alksjoflkajsom 5y ago
      You might be right (I don't actually know what a HELO string is, I don't know anything about SMTP :). I was just going off how the author presented the data, as a tuple of four completely independent things. Of course the main point still stands, that the two schemas are exactly as normalized as each other. Edit: rereading the original post, the author mentions that "they forged...the HELO"--so perhaps there was indeed no relationship between HELO and IP here. But again, I don't know anything about SMTP, so this could be wrong.
      • Brian_K_White 5y ago
        I do know about smtp and you were right regardless, because the author was talking about 4 database fields, not smtp. The details of smtp are irrelevant.
• daly 5y ago
  We had a test database that contained 1 record. Nobody paid much attention since the focus was on the problem, not the database. The database included several newly developed "stored procedures". Time elapsed... and it was nearing the time to ship the code. So we tried to populate the database. But we could not. It turned out that the stored procedures would only allow a single record in the database. Since a portion of the "business logic" depended on the stored procedures... well, things got "delayed" for quite a while... and we ended up having a major re-design of the back end. Fun times.
  • hinkley 5y ago
    Why do so many of us “forget” to populate the database with representative quantities of data until after we’ve baked in all of our poor design decisions?
• bizzarojerry 5y ago
  What a bizarre post from an influential blogger so confidently making assertions that are wildly contested even among HN users.
• jeffrallen 5y ago
  I did this (bad schema, too much scanning) too. I also ran newfs on a running Oracle database. (That did vastly less damage than you would have thought, actually.)
• an9n 5y ago
  Wow what a twist. Somebody call M Night Shyamalan!
• arein3 5y ago
  to me the first implementation seems the best, it just needed a multi column index the 3NF "optimized" form seems worse and unreadable. I would advise to normalize only when it's needed(complex/evolving model), in this case it was not needed
• temporallobe 5y ago
  This was great. Clever twist at the end. I cringed a little because some of those mistakes looks like stuff I would do even now. I have a ton on of front/back end experience in a huge variety of languages and platforms, but I am NOT a database engineer. That’s a specific skillset I never picked up nor, admittedly, am I passionate about. Sorry to go off on a tangent, but it also brings to mind that even so-called experts make mistakes. I watch a lot of live concert footage from famous bands from the 60s to the 90s, and as a musician myself, I spot a lot of mistakes even among the most renowned artist…with the sole exception of Rush. As far as I can tell, that band was flawless and miraculously sounded better live than on record!
• deleted 5y ago
  [deleted]
• ummonk 5y ago
  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".
• savant_penguin 5y ago
  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
• dilyevsky 5y ago
  The real take away here is DB performance optimization (imo performance in general) is freaking hard and even people who’d successfully optimized something like OP often totally misunderstand why it actually worked. Pay for an expert advice
• ehershey 5y ago
  Proud of myself for recognizing the conceit (pun intended) here pretty quickly. Good point in the end too!
• hermitwriter 5y ago
  I wonder how many people here are throwing stones while living in glass houses.
  • ummonk 5y ago
    I’m glad people are critiquing the technical parts of the post because it had me questioning everything I know about databases, which really isn’t a whole lot.
• teen 5y ago
  They could have just made a hash of the 4 values and indexed on it...
• TomGullen 5y ago
  You don’t need to store an IP in a different table as ID/IP, IP can be represented as 128 bit integer. A small “mistake” I’ve made before that makes some things a little more annoying - not that it’s particularly important
• ashishb 5y ago
  I once ended up doing 'rm -rf *' by mistake - https://ashishb.net/tech/my-rm-rf-moment/ https://ashishb.net/tech/my-rm-rf-moment/
• deleted 5y ago
  [deleted]
• darepublic 5y ago
  > what about you? Will try to keep making money coding
• jimmyed 5y ago
  Why not put a hash index on the four columns? By normalising, you get an index but maybe b tree, which isn't best here for exact matching?
• zmmmmm 5y ago
  It's clear that she didn't know the first thing about databases. In fact its clear even her current self is pretty naive (almost certainly a few indexes on the first solution would have solved it, or if that was tried and didn't work its a giant hole in the story). She's being quite open and vulnerable here and its important to respect that. So why was the job given to her at the time? Why wasn't there a mentor or lead or someone else to review it? Who thought it was a good idea? Even 5 minutes with an experienced DBA or senior engineer would have saved her this whole episode and given her a huge boost in learning. She seems to have come out of it ok, but how brutal it must be for young people thrown into a bear cage like that and then finding themselves responsible for huge issues and impacts and coming away calling themselves "terrible". Of course, it was 20 years ago, but are things any better now? I hope so, but I'm not sure.
  • fouc 5y ago
    The post has a link to an update at the bottom for you if you missed it
• throwaway2077 5y ago
  >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! nice to read someone smart feeling the same way I do about that kind of shit.
• banku_brougham 5y ago
  I've got a few war stories myself, I've seen things you people wouldn't believe. This case its important to recognize it was 2002, early days. This story just doesnt read the same in todays context.
• invincivlepvt 5y ago
  https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/social-media-marketing-jalandhar/ https://invincivlepvt.com/social-media-marketing-jalandhar/ https://invincivlepvt.com/email-marketing-jalandhar/ https://invincivlepvt.com/email-marketing-jalandhar/ https://invincivlepvt.com/social-media-optimisation-jalandhar/ https://invincivlepvt.com/social-media-optimisation-jalandha... https://invincivlepvt.com/sms-marketing-jalandhar-2/ https://invincivlepvt.com/sms-marketing-jalandhar-2/
• rukuu001 5y ago
  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).
• invincivlepvt 5y ago
  <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Google My Business</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Google My Business Jalandhar</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Digital Marketing</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Online Business Listing</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Google My Business Service</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Google My Business Service In Jalandhar</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Google My Business Agency</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Google My Business Agency Jalandhar</a> <a href="https://invincivlepvt.com/google-my-business-jalandhar/ https://invincivlepvt.com/google-my-business-jalandhar/" rel="nofollow">Digital Marketing Jalandhar</a>
• oblib 5y ago
  I'm probably going to get smacked a bit for this but a lot of the comments here focus on a better fix for the 20 year old problem the author relates only in order to illustrate a point and that's a very HN kind of response. And I get it. Years ago someone ask one of my grandkids what I did for a living and he told them "He's a fix it guy". That's because when he'd come over I'd be working on something and he'd ask what I was doing and I'd tell him "I'm fixing" this or that. There are a lot of "fix it" people here. It's what we do.
• Labo333 5y ago
  What is worse is Prisma obscuring actual SQL queries with a query language that puts usability first and allows you to quickly build prototypes that just don't scale for n=1000.
• chrisbrandow 5y ago
  I’m so grateful for rachelByTheBay.
• raxxorrax 5y ago
  > this crazy vendor system charged by the row or somesuch I don't really understand the text, but from what I could gather this is far worse than a forgotten index. You should set them in most cases, but it doesn't really reduce lookup speed if you don't also add foreign keys.
• HeavyStorm 5y ago
  I do think the author still has misconceptions over how a database works, and I'm kind astonished. No, your "poor" database system won't go through every row when you are querying a varchar column. As long as it is indexed, the database will perform a index scan of some sort. Assume O(logn) as the average complexity for most databases. Of course, if the data on a column is repeated a lot, normalizing it would bring some advantages, but that would also include performing a join,which isn't cheap. The key takeaway for me is: let's not let this Rachel person touch databases ever again.
  • tyho 5y ago
    This is exactly the attitude warned against in TFA.
  • ynx 5y ago
    Rachel is a very, very smart lady with a hell of a track record, who also has strong ethics, something the world needs more of. I think you missed the point of the article. Your reply must have felt great for you to write, though I'm not sure anyone got anything out of that but you.
  • dang 5y ago
    Personal attacks will get you banned here. Please review the site guidelines and make your substantive points without swipes in the future. https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html
• alkz 5y ago
  Guess this was one of the first implementations of greylisting :) This was not an issue of being in a vacuum, it was an issue of not reading documentation beforehand, which for some reason is acceptable in the development world I don't think there are so many people attempting to perform surgery or to build a bridge without reading a number of textbooks on the topic. And this is why back in the day everyone said RTFM
• larodi 5y ago
  people in IT are loners, lone wolfs, lone sharks, sometimes lone and also self centered. for the reasons in the article, and then for so many other reasons. for the reason that the online education favors 'am top learner on my own' principle. etc. the under-35-thing is just another reason. another is the ever-so-new technologies that obliterate all previous knowledge of obvious stuff. another is the fact that 'history of computing' is not something that you need to learn to start doing shiny webpages. then there's the reason that so many ppl bash at universities, at in-person schooling, at work-together places (a.k.a offices). and because someone is going to ask me what I did to change it - here's what: for almost 20 years now i've been teaching introductory or intermediate classes in-person to hundreds of students, and observing what helps them learn as fast as possible. trying to speed it up, to assist the process. i cana tell you one thing - working together and understanding that you can always learn from someone that is next to your shoulder is of paramount importance. after all - what good is gender or race diversity in the workplace, if you effectively do not know how to work along these people...? but this is too long for just a post here. the ability, the skill, to be able to work with someone, with everyone, is what the vast majority of IT top-coders lack. and lack badly and painfully (for those around mostly). look around yourself for examples... ... the day before I was thinking, that Larry Wall's timtoady principle is really about celebrating diversity in IT, about different approaches to the same problem. and not about celebrating diversity in Perl or any other language.
• ttfkam 5y ago
  Not terribly convinced by arguments about normalization for normalization's sake. As with most things, the definitive answer is "it depends". It's not the 1990s anymore. https://youtu.be/8Fb5Qgpr03g https://youtu.be/8Fb5Qgpr03g
• stolsvik 5y ago
  The normalized solution sounds like a performance nightmare?! Stick the original data into something like Clickhouse? (After checking whether indexing would fix it for a few years more)
• lbill 5y ago
  It's quite hard to create something that works optimally on a first try. I think I never managed to do so! The first schema was slow, but hey, it worked, good job! That is a huge first step. Modifying it so it runs much faster can definitely come next.