8 ms·
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
by 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 agoThe point is she was being hyper critical of her past schema to make a larger point.
- mwcampbell 5y agoAnd 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 agoIt 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 agoIn 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 agoLook at the way we screen resumes, or valorize work conditions incompatible with having family obligations. Ageism in the tech industry is rife.
- bialpio 5y agoOr how leetcode heavy hiring favors folks that tend to be more fresh out of college & folks who have time to prepare for them.
- erikerikson 5y agoRole count pyramids, volume and inflexibility of working hours, abusive management techniques, a constant treadmill of the new hotness, more... Some offices avoid many of the challenging patterns but not the vast majority. A lot of it is natural result of the newness of the industry and the high stakes of investment. Many of the work and organizational model assumptions mean that offering flexibility to employees complicates the already challenging management role. Given the power of those roles, this means workers largely must fit into the mold rather than designing a working relationship that meets all needs. As the needs change predictably over time a mounting pressure develops. I'm only 17 years in but it has gotten harder and harder to find someone willing to leave me to simply code in peace and quiet and dig my brain into deep and interesting problems.
- dehrmann 5y agoI have mixed feelings about this. On one hand, I appreciate the larger point, and pointing out mistakes again goes against that point. On the other, the post had so many technical details it overshadowed the larger point, especially for something that wasn't obviously broken.
- bitexploder 5y agoYeah… technically that’s (the solution) normalization, but really not a bad design originally. If you never want a particular atom of data repeated, yes you have to normalize each column, and that is efficient in the long run for storage size, but in more normal data storage that still means a lot of joins and you still want indices. On a modern SSD you could use that one table, throw on some indices as you suggest and not notice it again until you hit millions of rows. Anything you are doing conditional logic or joins on in SQL usually needs some sort of index.
- themgt 5y agoAlso you could just store IPv4 as an unsigned INT directly, rather than strings in a different table and an unsigned INT as a foreign key.
- tptacek 5y agoI have regretted every single time I've gotten cute about storing IP addresses as scalars in SQL.
- ryukafalz 5y agoWhat issues did you run into? Having admittedly never done this before, it feels like whenever you need the dotted-quad IP you could just query: SELECT INET_NTOA(ip) FROM ips WHERE... ...in MySQL at least. (Which might be why MySQL has this function built in?) I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue?
- Ginden 5y ago> I guess if you ever need to match on a specific prefix storing the numeric IP might be an issue? If you want to search for range, you can do: SELECT INET_NTOA(ip) FROM ips WHERE ip > INET_NTOA(:startOfRange) AND ip < INET_NTOA(:endOfRange)
- soheil 5y agoBecause as shocking as this might sound like unlike the characters in the movie Matrix I don't have a built-in INET_NTOA function in my retina to see the string form of the IP addresses when glancing at table rows of a large table looking for patterns or a certain IP.
- ryukafalz 5y agoHow often are you looking at the full contents of a table vs. looking at the results of a query that you could throw an INET_NTOA onto?
- tomc1985 5y agoIdunno the real villain is that bullshit, costly "per-row" pricing
- deleted 5y ago[deleted]
- da39a3ee 5y agoOK so change the article's technical solution from "normalize" to "add index". That doesn't change the point of the article.
- deleted 5y ago[deleted]
- havkd 5y agoIs this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?
- rustc 5y agoMoving the data into another table would still require indexes: one on the original table's column (which now stores the new id) and one on the new table's primary key. In most cases I'd expect just adding an index to the original table to be more efficient, but it depends on the type of the original column and if some data could be de-duplicated by the normalization.
- taeric 5y agoAn index is often easily viewed as another table. So, should. (Some subtleties on projected values and such, but the point stands.)
- havkd 5y agoThen this post is very misleading.
- taeric 5y agoI wouldn't get too vehement on it. Knowing how to normalize isn't a bad thing. And, if you intricately know your write and read needs, you may be able to assist the work more appropriately.
- jameshart 5y agoYes, the proposed ‘better’ structure basically amounts to building your own indexes. Normally, I’d assume it is better to use the RDBMS’s own engine to do that, don’t roll your own. There may well be some subtlety to the indexing capabilities of MySQL I’m unaware of though - could easily imagine myself making rookie mistakes like assuming that it has same indexing capabilities. So, to the post’s point - if I were working on a MySQL db I would probably benefit from an old hand’s advice to warn me away from dangerous assumptions. On the other hand I also remember an extremely experienced MS SQL Server DBA giving me some terrible advice because what he had learned as a best practice on SQL Server 7 turned out to be a great way to not get any of the benefits of a new feature in SQL Server 2005. Basically, we’re all beginners half the time.
- zhoujianfu 5y agoPhew.. I was reading this post thinking “is basically every table I’ve ever made wrong?!”
- topher_t 5y agoI definitely got a spike of imposter syndrome thinking, of fuck I've been in software this long and haven't learned this yet?!
- mst 5y agoFor modern databases, the extra normalisation is an incremental optimisation that you usually won't need. But I think she's if anything been in the industry longer than I have and the versions of mysql I first ran in production were a different story.
- mewpmewp2 5y agoI 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 me for that reason. In fact if I had to guess multiple tables would've performed worse. That is if I'm understanding the problem correctly at all.
- gavinray 5y agoThis has been the advice given to me by Postgres experts in a similar scenario: "If you want to efficiently fuzzy-search through a combination of firstname + lastname + (etc), it's faster to make a generated column which concatenates them and index the generated column and do a text search on that." (Doesn't have to be fuzzy-searching, but just a search in general, as there's a single column to scan per row rather than multiple) But also yeah I think just a compound UNIQUE constraint on the original columns would have worked I'm pretty sure that the degree of normalization given in the end goes also well beyond 3rd-Normal-Form. I think that is 5th Normal Form/6th Normal Form or so, almost as extreme as you can get: https://en.wikipedia.org/wiki/Database_normalization#Satisfying_6NF https://en.wikipedia.org/wiki/Database_normalization#Satisfy... The way I remember 3rd Normal Form is "Every table can stand alone as it's own coherent entity/has no cross-cutting concerns". So if you have a "product" record, then your table might have "product.name", "product.price", "product.description", etc. The end schema shown could be described as a "Star Schema" too, I believe (see image on right): https://en.wikipedia.org/wiki/Star_schema#Example https://en.wikipedia.org/wiki/Star_schema#Example This Rachel person is also much smarter than I am, and you can make just about anything work, so we're all bikeshedding anyways!
- tkot 5y agoWould it not prevent some optimizations based on statistics regarding the data distribution, like using the most selective attribute to narrow down the rows that need to be scanned? I'm assuming there are 2 indexes, 1 for each column that gets combined. Let's say you know the lastname (Smith) but only the first letter of the firstname (A) - in the proposed scenario only the first letter of the firstname helps you narrow down the search to records starting with the letter (WHERE combined LIKE "A%SMITH"), you will have to check all the rows where firstname starts with "A" even if their lastname is not Smith. If there are two separate indexed columns the WHERE clause will look like this: WHERE firstname like "A%" AND lastname = "Smith" so the search can be restricted to smaller number of rows. Of course having 2 indexes will have its costs like increased storage and slower writes. Overall the blog post conveys a useful message but the table example is a bit confusing, it doesn't look like there are any functional dependencies in the original table so pointing to normalization as the solution sounds a bit odd. Given that the query from the post is always about concrete values (as opposed to ranges) it sounds like the right way to index the table is to use hash index (which might not have been available back in 2002 in MySql).
- jeroenhd 5y agoBased 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 that these days you'd just add an index on the text column and go with it. Depending on your index type and data, the end result might be that the database turns the table into third normal form by creating separate lookup tables for strings, but hides it from the user. It could also create a smarter index that's less wasteful, but the end result is not so dissimilar. Manually doing these kinds of optimisations these days is usually a waste of effort or can even cause performance issues (e.g. that post on the front page yesterday about someone forgetting to add an index because mysql added them automatically). 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. [0]: http://download.nust.na/pub6/mysql/doc/refman/4.1/en/create-index.html http://download.nust.na/pub6/mysql/doc/refman/4.1/en/create-...
- willvarfar 5y agoKnowing only what we can guess about the actual problem, i’d say indexing ip ought be enough to make everything super fast.
- hn_throwaway_99 5y agoThe fact remains that whether the text column existed on her original table, or whether it was pulled out to a normalized table, literally all of the same constraints would apply (e.g. max char length, any other underlying limitations of indexing). The issue is that her analysis of what the issue was with her original table is completely wrong, and it's very weird given that the tone her "present" self is that it's so much more experienced and wise than her "inexperienced, naive" self. My point is that she should give her inexperience self a break, all that was missing from her original implementation were some indexes.
- 5y ago
- erikerikson 5y agoClearly it worked. However... Consider the rows `a b c d e` and `f b h i j`. How many times will `b` be stored in the two formulations? 2 and 1, right? The data volume has a cost. Consider the number of cycles to compare a string versus an integer. A string is, of course, a sequence of numbers. Given the alphabet has a small set of symbols you will necessarily have a character repeated as soon as you store more values than there are characters. Therefore the database would have to perform at least a second comparison. I imagine you're correctly objecting that in this case the string comparison happens either way but consider how this combines with the first point. Is the unique table on which the string comparisons are made smaller? Do you expect that the table with combinations of values will be larger than the table storing comparable values? Through this, does the combinations table using integer comparisons represent a larger proportion of the comparison workload? Clearly she found that to be the case. I would expect it to be the case as well.
- globular-toast 5y ago> Clearly it worked. No it didn't. Someone else put an index on the main table and that worked.
- erikerikson 5y agoThe article declared that it was running in production and satisfying the requirement. Do you have a different definition of working? I say that to clarify what I was attempting to communicate by "it worked". I think you're remarking about the relationship between normalized tables and indexes. That relationship does exist in some databases. Please say more if I'm missing your point.
- lern_too_spel 5y agoGP's point is that the final schema has a primary key index on the main table that solved the problem, and then it has a bunch of useless additional tables. The solution was just to add a primary key index on the main table. Adding the additional tables just slows down the query because it now has to do five index lookups instead of one.
- ignoramous 5y ago> This post is bizarre ... Which is nonsense ...but from her description I would say it wasn't. This is what the post is about. And btw, if they hadn't normalized those tables, it'd instead have been pointed out to them that their "post is bizzare..."
- hoppla 5y agoI usually store IPv4 as unsigned int, and IPv6 as two 64 bit unsigned ints. But supporting both variants require that I have an a table for each type and a glue table to reference the record in the correct table. Storing simple stuff quickly turns complicated…
- mewpmewp2 5y agoIs there much point in optimising this as opposed to keeping it in strings, at what QPS do you think it would matter and how much?
- hoppla 5y agoThe main reason to store it as numbers is because I select records based on what subnet an IP address is in. I can only do this when I use integers.
- noctune 5y agoI especially feel the proposed solution could be problematic given that the data is supposed to be transient. How are keys deleted from the "id | value" tables when rows are deleted from the main table? Does it just keep accumulating rows or is there something like a GC query running occasionally that deletes all unreferenced values?
- codazoda 5y agoAs a 25 year “veteran” programmer, I’m glad I’m not the only one that likes the original schema (with indexes added). A fully normalized table is a lot more difficult to read, troubleshoot, and reason about. You would need a pretty good query to poke around the data.
- mst 5y agoWhen I first started using postgres I was amazed at how much of what I thought was "database knowledge" was actually "working around limitations of the versions of mysql I'd been using" knowledge. These days, sure, the original schema plus indices would work fine on pretty much anything I can think of. Olde mysqls were a bit special though.
- someonewhocar3s 5y agoI was kinda surprised by the last chapter, suddenly this is about 'advancing in programming as a woman', and it's over! Like the last few chapters make sense in that view. What I want to know is how she went from a strong database format (ID + data, keyed) into a weak database model (not a database... is a key-value store) which is likely to be much slower, but also, this is what happens to varchars under the hood already (the rows only hold pointers, to where the strings/objects are placed, rather than inlining varchars) (at least in litesql!), so optimally it's only two dereferences instead of one.. Like, eh, this programmer went through an anti-learning process somehow, and came up with a senseless optimisation. The post ends midway through the learning!
- dang 5y agoPlease make your substantive points without calling names. This is in the site guidelines: https://news.ycombinator.com/newsguidelines.html https://news.ycombinator.com/newsguidelines.html