News for hackers. Stuff that matters.
Re:A terrible schema from a clueless programmer by hn_throwaway_99 on Sunday November 07, 2021 @10:35AM (#29140281)
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.

Re:A terrible schema from a clueless programmer by dang on Monday November 08, 2021 @11:13AM (#29150774)
Please make your substantive points without calling names. This is in the site guidelines:

https://news.ycombinator.com/newsguidelines.html

Re:A terrible schema from a clueless programmer by jeroenhd on Sunday November 07, 2021 @11:36AM (#29140890)
Based on this piece of old documentation I found [0] for MySQL 3.23 (the most recent version in 2002 as far as I can tell), certain types of indices were only available on certain types of engines. Furthermore, columns were restricted to 255 characters, which may be too short for some of the fields saved in the database.

Modern databases abstract away a lot of database complexity for things like indices. It's true 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-...

Re:A terrible schema from a clueless programmer by hn_throwaway_99 on Sunday November 07, 2021 @12:22PM (#29141387)
The 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.

Re:A terrible schema from a clueless programmer by mst on Sunday November 07, 2021 @01:30PM (#29142056)
Multiple-long-column compound indices sucked in old mysqls if you could even convince it to use them in the first place.

Being able to look up each id via a single-string unique index would've almost certainly worked much better in those days.

Re:A terrible schema from a clueless programmer by hn_throwaway_99 on Sunday November 07, 2021 @02:03PM (#29142402)
I used MySql a lot back then, had many multi-column indexes, and never had an issue.

More importantly, given the degree of uniqueness likely to be present in many of those columns (like the email addresses), she could have gotten away with not indexing on every column.

Re:A terrible schema from a clueless programmer by mst on Sunday November 07, 2021 @02:22PM (#29142558)
Your first sentence was, I'm afraid, very much not a universal experience.

Your second is quite possibly true, but would have required experimentation to be sure of, and at some point "doing the thing that might be overkill but will definitely work" becomes a more effective use of developer time, especially when you have a groaning production system with live users involved.

Re:A terrible schema from a clueless programmer by e12e on Sunday November 07, 2021 @01:14PM (#29141913)
Based on: https://web.mit.edu/databases/mysql/mysql-3.23.6-alpha/Docs/...

I'd say there's very little performance gain in normalizing (it usually goes the other way anyway: normalize for good design, avoiding storing multiple copies of individual columns; de-normalize for performance).

I'm a little surprised by the tone of the article - sure, there were universities that taught computer science without a database course - but it's not like there weren't practical books on dB design in the 90s and onward?

I guess it's meant as a critique of the mentioned, but not linked other article "being discussed in the usual places".

Re:A terrible schema from a clueless programmer by willvarfar on Sunday November 07, 2021 @11:40AM (#29140944)
Knowing only what we can guess about the actual problem, i’d say indexing ip ought be enough to make everything super fast.
Re:A terrible schema from a clueless programmer by mixmastamyk on Sunday November 07, 2021 @04:54PM (#29143856)
> All that doesn't mean it was probably a terrible design back when it was written. We're talking database tech of two decades ago, when XP had just come out and was considered a memory hog because it required 128MB of RAM to work well.

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

Re:A terrible schema from a clueless programmer by mewpmewp2 on Sunday November 07, 2021 @11:22AM (#29140751)
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

[email protected][email protected]

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.

Re:A terrible schema from a clueless programmer by gavinray on Sunday November 07, 2021 @11:33AM (#29140858)
This 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#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

This Rachel person is also much smarter than I am, and you can make just about anything work, so we're all bikeshedding anyways!

Re:A terrible schema from a clueless programmer by dghf on Monday November 08, 2021 @06:36AM (#29148283)
>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

The original schema is also in 5NF, unless there are constraints we aren't privy too.

5NF means you can't decompose a table into smaller tables without loss of information, unless each smaller table has a unique key in common with the original table (in formal terms, no non-trivial join dependencies except for those implied by the candidate key(s)).

The original table appears to meet this constraint: you could break it down into, e.g., four tables, one mapping the quad id to the IP address, one mapping it to the HELO string, etc. However, each of these would share a unique constraint with the original table, the quad id; hence the original table is in 5NF.

As for 6NF, I don't think the revised schema meets that: 6NF means you can't losslessly decompose the table at all. In the revised schema, the four tables mapping IP id to IP address etc. are in 6NF, but the table mapping quad id to a unique combination of IP id, HELO id, etc. is not: it could be decomposed into four tables similarly to how the original table could be.

(Interestingly, if the original table dropped the quad ID column and just relied on a composite primary key of IP address, HELO string, FROM address and TO address, it would be in 6NF.)

Re:A terrible schema from a clueless programmer by tkot on Sunday November 07, 2021 @03:38PM (#29143295)
Would 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).

Re:A terrible schema from a clueless programmer by gavinray on Sunday November 07, 2021 @04:37PM (#29143734)
I won't pretend to be an expert in this realm, but see here:

https://www.postgresql.org/docs/current/textsearch-tables.ht...

Specifically, the part starting at the below paragraph, the explanation for which continues to the bottom of the page:

  "Another approach is to create a separate tsvector column to hold the output of to_tsvector. To keep this column automatically up to date with its source data, use a stored generated column. This example is a concatenation of title and body, using coalesce to ensure that one field will still be indexed when the other is NULL:"
I have been told by PG wizards this same generated, concatenated single-column approach with an index on each individual column, plus the concatenated column, is the most effective way for things like ILIKE search as well.

But I couldn't explain to you why

Re:A terrible schema from a clueless programmer by tkot on Monday November 08, 2021 @12:23PM (#29151619)
Oh, so by fuzzy search you mean full text search, not just a simple 'LIKE A%' pattern - it's an entirely different kind of flying altogether (https://www.youtube.com/watch?v=3qNtyfZP8bE).

I don't know what kind of internal representation is used to store tsvector column type in PG, but I think GIN index lookups should be very simple to parallelize and combine (I believe a GIN index would basically be a map [word -> set_of_row_ids_that_contain_the_word] so you could perform them on all indexed columns at the same time and then compute intersection of the results?). But maybe two lookups in the same index could be somehow more efficient than two lookups in different indexes, I don't know.

I'm still sceptical about the LIKE/ILIKE scenario though. "WHERE firstname LIKE 'A%' and lastname LIKE 'Smith' " can easily discard all the Joneses and whatnot before even looking at the firstname column, whereas "WHERE combined_firstname_and_lastname LIKE 'A%SMITH' " will only be able to reject "Andrew Jones" after reading the entire string.

Re:A terrible schema from a clueless programmer by ryandrake on Sunday November 07, 2021 @11:35AM (#29140883)
Also, the "better" solution assumes IPv4 addresses and is not robust to a sudden requirements change to support IPv6. Best to keep IP address as a string unless you really, REALLY need to do something numerical with one or more of the octets.
Re:A terrible schema from a clueless programmer by pilif on Sunday November 07, 2021 @12:26PM (#29141431)
Based on my experience I disagree. If there is a native data type that represents your data, you should really use it.

It will make sure that incorrect data is detected at the time of storage rather than at some indeterminate time in the future and it will likely be more efficient than arbitrary strings.

And in case of IP addresses, when you are using Postgres, it comes with an inet type that covers both ipv4 and ipv6, so you will be save from requirement changes

Re:A terrible schema from a clueless programmer by johannes1234321 on Sunday November 07, 2021 @05:31PM (#29144211)
Keeping IP addresses as string isn't trivial. You can write those in many ways. Even with IPv4. IPv6 just brings new variations. And when talking about migration to IPv6 you have to decide if you keep IPv4 Addresses as such or prefix with ::ffff:.

In the end you have to normalize anyways. Some databases have specific types. If not you have to pick a scheme and then ideally verify.

Re:A terrible schema from a clueless programmer by riquito on Sunday November 07, 2021 @12:27PM (#29141447)
That way you can get false positives unless you concatenate using a non-valid character in the protocol

[email protected] [email protected] [email protected] [email protected]

Btw, storing just the domains, or inverting the email strings, would have speed up the comparison

Re:A terrible schema from a clueless programmer by kelnos on Sunday November 07, 2021 @01:12PM (#29141896)
Right, and when you do that, you don't even need a RDBMS. An key-value store would suffice. This essentially just becomes a set! Redis or memcached are battle-tested workhorses that would work even better than a relational DB here.

But this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn't be comfortable using in production.

Re:A terrible schema from a clueless programmer by dehrmann on Sunday November 07, 2021 @03:06PM (#29142994)
> this was also back in the early '00s, when "data store" meant "relational DB", and anything that wasn't a RDBMS was probably either a research project or a toy that most people wouldn't be comfortable using in production.

Indeed; her problem looks to have been pre-memcached.

Re:A terrible schema from a clueless programmer by dehrmann on Sunday November 07, 2021 @12:46PM (#29141654)
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 by jeremyjh on Sunday November 07, 2021 @01:48PM (#29142248)
Well she did say she had indexes on the new table. It could have been fixed with an index on the previous table, but a new table with indexes also fixed it.
Re:A terrible schema from a clueless programmer by codazoda on Sunday November 07, 2021 @12:29PM (#29141460)
As 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.
Re:A terrible schema from a clueless programmer by mst on Sunday November 07, 2021 @01:32PM (#29142088)
When 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.

Re:A terrible schema from a clueless programmer by gfodor on Sunday November 07, 2021 @10:48AM (#29140407)
The point is she was being hyper critical of her past schema to make a larger point.
Re:A terrible schema from a clueless programmer by mwcampbell on Sunday November 07, 2021 @10:54AM (#29140470)
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.
Re:A terrible schema from a clueless programmer by erikerikson on Sunday November 07, 2021 @11:30AM (#29140833)
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.
Re:A terrible schema from a clueless programmer by sokoloff on Sunday November 07, 2021 @11:38AM (#29140920)
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.
Re:A terrible schema from a clueless programmer by wbl on Sunday November 07, 2021 @11:44AM (#29140984)
Look at the way we screen resumes, or valorize work conditions incompatible with having family obligations. Ageism in the tech industry is rife.
Re:A terrible schema from a clueless programmer by bialpio on Sunday November 07, 2021 @12:10PM (#29141259)
Or how leetcode heavy hiring favors folks that tend to be more fresh out of college & folks who have time to prepare for them.
Re:A terrible schema from a clueless programmer by erikerikson on Sunday November 07, 2021 @12:16PM (#29141325)
Role 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.

Re:A terrible schema from a clueless programmer by dehrmann on Sunday November 07, 2021 @12:58PM (#29141765)
I 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.
Re:A terrible schema from a clueless programmer by zhoujianfu on Sunday November 07, 2021 @11:17AM (#29140700)
Phew.. I was reading this post thinking “is basically every table I’ve ever made wrong?!”
Re:A terrible schema from a clueless programmer by topher_t on Sunday November 07, 2021 @01:22PM (#29141979)
I definitely got a spike of imposter syndrome thinking, of fuck I've been in software this long and haven't learned this yet?!
Re:A terrible schema from a clueless programmer by mst on Sunday November 07, 2021 @01:34PM (#29142104)
For 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.

Re:A terrible schema from a clueless programmer by bitexploder on Sunday November 07, 2021 @11:26AM (#29140796)
Yeah… 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.

Re:A terrible schema from a clueless programmer by noctune on Sunday November 07, 2021 @12:15PM (#29141306)
I 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?
Re:A terrible schema from a clueless programmer by erikerikson on Sunday November 07, 2021 @11:48AM (#29141030)
Clearly 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.

Re:A terrible schema from a clueless programmer by globular-toast on Sunday November 07, 2021 @12:15PM (#29141314)
> Clearly it worked.

No it didn't. Someone else put an index on the main table and that worked.

Re:A terrible schema from a clueless programmer by erikerikson on Sunday November 07, 2021 @12:35PM (#29141540)
The 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.

Re:A terrible schema from a clueless programmer by lern_too_spel on Sunday November 07, 2021 @05:02PM (#29143926)
GP'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.
Re:A terrible schema from a clueless programmer by natmaka on Sunday November 07, 2021 @09:36PM (#29145677)
Using IDs may lead to a performance gain if it leads to a smaller database: less bytes to read/write/cache => less I/O volume, and better cache hits compensating the potentially higher amounts of seeks on mass storage. This is especially true when seeks are quick (SSD...).

Therefore as long as the size data type (C language's "sizeof") used for an ID is inferior to the average size of the column contents then using an ID will very probably lead to a performance gain.

On some DB states and usage patterns (where commonly used data+index cannot fit in RAM: the caches (DB+OS) hit ratios are < to .99) this gain will be somewhat proportional (beware of diminishing returns) to the value of the ratio (total data+index size BEFORE using IDs)/(total data+index size AFTER using IDs).

Creating queries then becomes more difficult (one has to use 'JOIN'), however there are ways alleviate this: using views, "natural join"...

Some modern DB engines let you put data into an index, in order to spare an access to the data when the index is used (Postgresql: see the "INCLUDE" parameter of the "CREATE INDEX"). As far as I understand using a proper ID (<=> on average smaller than the data it represents) will also lead to a gain(?)

Re:A terrible schema from a clueless programmer by themgt on Sunday November 07, 2021 @11:27AM (#29140802)
Also 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.
Re:A terrible schema from a clueless programmer by tptacek on Sunday November 07, 2021 @11:29AM (#29140825)
I have regretted every single time I've gotten cute about storing IP addresses as scalars in SQL.
Re:A terrible schema from a clueless programmer by ryukafalz on Sunday November 07, 2021 @11:53AM (#29141092)
What 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?

Re:A terrible schema from a clueless programmer by Ginden on Sunday November 07, 2021 @12:22PM (#29141384)
> 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)
Re:A terrible schema from a clueless programmer by soheil on Sunday November 07, 2021 @12:12PM (#29141274)
Because 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.
Re:A terrible schema from a clueless programmer by ryukafalz on Sunday November 07, 2021 @12:53PM (#29141711)
How 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?
Re:A terrible schema from a clueless programmer by soheil on Sunday November 07, 2021 @02:07PM (#29142436)
Many. You also expect me to remember how to spell INET_NTOA and on what fields to use it on. What if I wanted do a quick "SELECT * FROM"? I barely know how to spell English words what makes you think I'm going to remember how to spell INET_NTOA.
Re:A terrible schema from a clueless programmer by Lammy on Sunday November 07, 2021 @03:29PM (#29143209)
I realize you’re describing a general problem and not just this particular example, but fwiw NTOA and friends got a lot easier for me once I learned enough that I stopped trying to “remember how to spell” them and started thinking about them in terms of the underlying concepts, like “Network (as in big-endianness) to ASCII”. Learning the significance of the term “address family” was a big clue for me for self-directed learning in this space :)
Re:A terrible schema from a clueless programmer by mixmastamyk on Sunday November 07, 2021 @05:04PM (#29143950)
dbeaver CE is free.
Re:A terrible schema from a clueless programmer by tomc1985 on Sunday November 07, 2021 @11:38AM (#29140924)
Idunno the real villain is that bullshit, costly "per-row" pricing
Re:A terrible schema from a clueless programmer by hoppla on Sunday November 07, 2021 @12:05PM (#29141218)
I 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…
Re:A terrible schema from a clueless programmer by mewpmewp2 on Sunday November 07, 2021 @06:22PM (#29144565)
Is 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?
Re:A terrible schema from a clueless programmer by hoppla on Wednesday November 10, 2021 @01:52PM (#29178740)
The 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.
Re:A terrible schema from a clueless programmer by ignoramous on Sunday November 07, 2021 @11:51AM (#29141079)
> 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..."

Re:A terrible schema from a clueless programmer by da39a3ee on Sunday November 07, 2021 @10:54AM (#29140469)
OK so change the article's technical solution from "normalize" to "add index". That doesn't change the point of the article.
Re:A terrible schema from a clueless programmer by havkd on Sunday November 07, 2021 @11:06AM (#29140563)
Is this correct? Would indexing the columns instead of moving the values to another table lead to the same increase in performance?
Re:A terrible schema from a clueless programmer by rustc on Sunday November 07, 2021 @11:20AM (#29140723)
Moving 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.

Re:A terrible schema from a clueless programmer by loeg on Sunday November 07, 2021 @11:11AM (#29140626)
The same O(N) -> O(log N) improvement for queries, yes. The constant factor on the separate tables might be better. It’s also a more complicated design.
Re:A terrible schema from a clueless programmer by mst on Sunday November 07, 2021 @01:36PM (#29142124)
Given this is a "from the start of her career" story, I'm guessing she was running similar versions of mysql to the versions I started with, and if my guess is correct then probably not.

On anything you're likely to be deploying today, just throwing a compound index at it is likely quite sufficient though.

Re:A terrible schema from a clueless programmer by taeric on Sunday November 07, 2021 @11:10AM (#29140619)
An index is often easily viewed as another table. So, should.

(Some subtleties on projected values and such, but the point stands.)

Re:A terrible schema from a clueless programmer by jameshart on Sunday November 07, 2021 @12:25PM (#29141425)
Yes, 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.

Re:A terrible schema from a clueless programmer by rrrrrrrrrrrryan on Sunday November 07, 2021 @01:31PM (#29142069)
As a former SQL Server DBA, most SQL Server DBAs are absolute gurus on 1 or 2 specific versions of SQL server, and massively ignorant about anything newer.

It's a strange role where, in order to do your job well, you kind of have to hyper-specialize in the specific version of the tech that your MegaCorp employer uses, which is usually a bit older, because upgrading databases can be extremely costly and difficult.

Re:A terrible schema from a clueless programmer by havkd on Sunday November 07, 2021 @11:12AM (#29140634)
Then this post is very misleading.
Re:A terrible schema from a clueless programmer by taeric on Sunday November 07, 2021 @11:35AM (#29140886)
I 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.
Re:A terrible schema from a clueless programmer by kofejnik on Sunday November 07, 2021 @11:34AM (#29140865)
It would be faster, I believe. As we’re only looking for existence, a single index traversal is all io we would need to do
Re:A terrible schema from a clueless programmer by adfgaertyqer on Sunday November 07, 2021 @11:13AM (#29140640)
Sure would! I think it would be marginally better, in fact, because you would just need to look at the index rather than five tables. Access would be more local.