231–240 of 493 posts
The ending is the most important part. > Now, what do you suppose happened to that clueless programmer who didn't know anything about foreign key relationships? > Well, that's easy. She just wrote this post for you. That's right, I was that clueless newbie who came up with a completely ridiculous abuse of a SQL database that was slow, bloated, and obviously wrong at a glance to anyone who had a clue. > My point is: E…
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, a…
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…
In other words, with enough empathy and patience, a clueless rookie can grow into a clueless senior engineer!
Rachel usually makes more sense than that. That's why people are nitpicking implementation details.
Earlier quoted context omitted.
The reason "the details are important" here are not because of the nitty gritty around what mistakes a "novice" programmer made. They are important because the present incarnation of the author is making all the wrong diagnoses about the problems with the original implementation, despite doing it with an air of "Yes, younger me was so naive and inexperienced, and present me is savvy and wise".
She explained the problem, the first not working solution and the second working solution as they really happened in 2002 as an example . The real point is the last part of the post. And it was not "how to properly implement an sql based filter for open proxies in your MTA".
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?
Normalization is important for deduplication, not only to index and compare a few short numbers instead of a few long string: those host names and email addresses are long and often repeated.
Earlier quoted context omitted.
MySQL could index strings in 2002. It would have worked fine.
"mysql could index strings" and "using a compound index over four varchar columns would've worked out well" are significantly different propositions.
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.
> lack of database optimization features That's a perfection description of mysql as of 2002.
http://web.archive.org/web/20020610031610/http://www.mysql.c...
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 compre…
One thing that worth taking into consideration is that this happened in 2002. When the databases were not in cloud, the ops was done by dba’s and key prefix compression thats omnipresent today was likely not that common or potentially not even implemented/available. But i don’t think the point of the post is whats right/wrong way of doing it. The point as mentioned by few here is that programmers makes mistakes. They…
There was a lot of pressure to relax 3NF as being too academic and not practical.
Around then, I had a friend who was using a pattern of varchar primary keys so that queries that just needed the (unique) name and not the metadata could skip the join. We all acted like he was engaging in the Dark Arts.
Earlier quoted context omitted.
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…
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.
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.