A terrible schema from a clueless programmer(rachelbythebay.com)
rachelbythebay.com
A terrible schema from a clueless programmer
http://rachelbythebay.com/w/2021/11/06/sql/
450 comments
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?
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?
> 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?
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.
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.
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.
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.
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.
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.
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 "[email protected]" to a new value "id1" has no effect on the relations in your table. Now instead of "[email protected]" 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.
Normalization does not mean "the same string can only appear once". Mapping the string "[email protected]" to a new value "id1" has no effect on the relations in your table. Now instead of "[email protected]" 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.
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.
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.
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.
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!
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!
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.
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.
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?
> 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?
<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.
(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.
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.
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.
> 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?
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?
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.
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.
> 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.
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.
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
Edit: typos
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.
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.
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.
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.
id | ip_address | from | to | content | updated_at | created_at
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.
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.
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.
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.
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.
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.
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.
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.
[deleted]
> 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.
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.
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.
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.
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
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.