11 ms·

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

by 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
> all major databases do these days Did everyone on HN miss that the database in question was whichever version of MySQL existed in 2002?
- mst 5y ago
  Which I was using in production at the time, and, yeah, what she ended up doing was a much better plan for that thing.
- jeremyjh 5y ago
  It actually doesn't matter. Even simple indexes would have made that schema work just fine, and MySQL could certainly index strings in 2002.
deleted 5y ago
[deleted]
silisili 5y ago
I think it depends a lot on the data. If those values like IP, From, To, etc keep repeating, you save a lot of space by normalizing it as she did. But strictly from a performance aspect, I agree it's a wash if both were done correctly.
- philliphaydon 5y ago
  Space is cheap now tho. Better to duplicate some data and avoid a bunch of joins than to worry about saving a few gb of space.
  - kasey_junk 5y ago
    This system was written in 2002.
  - acdha 5y ago
    It’s not that easy: you need to consider the total size and cardinality of the fields potentially being denormalized, too. If, say, the JOINed values fit in memory and, especially, if the raw value is much larger than the key it might be the case that you’re incurring a table scan to avoid something which stays in memory or allows the query to be satisfied from a modest sized index. I/O isn’t as cheap if you’re using a SAN or if you have many concurrent queries.
  - wvenable 5y ago
    Using space to avoid joins will not necessarily improve performance in an RDBMS -- it might even make it worse.
    - philliphaydon 5y ago
      The inverse of your statement is also true. Denormalizing the database to avoid duplicating data will not necessarily improve performance in an RDBMS - it might even make it worse.
nerdponx 5y ago
And even if this is somehow not an ideal use of the database, it's certainly far from "terrible", and to conclude that the programmer who designed it was "clueless" is insulting to that programmer. Even if that programmer was you, years ago (as in the blog post). Moreover, unless you can prove with experimental data that the 3rd-normal-form version of the database performs significantly better or solves some other business problem, then I would argue that refactoring it is strictly worse. There are good reasons not to use email addresses as primary or foreign keys, but those reasons are conceptual ("business logic") and not technical.
late2part 5y ago
Deleted
- emerongi 5y ago
  To a degree, this is true though. An engineer's salary is a huge expense for a startup, it's straight up cheaper to spend more on cloud and have the engineer work on the product itself. Once you're bigger, you can optimize, of course.
  - late2part 5y ago
    Deleted
    - emerongi 5y ago
      If this is a case from real life that you have seen, you should definitely elaborate more on it. Otherwise, you are creating an extreme example that has no significance in the discussion, as I could create similarly ridiculous examples for the other side. There are always costs and benefits to decisions. It seems that are you only looking at the costs and none of the benefits?
      - late2part 5y ago
        Deleted
- bigbillheck 5y ago
  > compute is cheap There was a whole thread yesterday about how a dude found out that it isn't: https://briananglin.me/posts/spending-5k-to-learn-how-database-indexes-work/ https://briananglin.me/posts/spending-5k-to-learn-how-databa... (also mentioned in the RbtB post)
  - late2part 5y ago
    When do the rest of the folks figure it out?
maddynator 5y ago
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 are costly and will be costly if in tech industry, we continue to boot experienced engineers… the tacit knowledge those engineers have gained wi ll not be passed on and this means more people have to figure things out by themselves
- adfgaertyqer 5y ago
  You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB. We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.
  - ipaddr 5y ago
    Just to make this fun. Adding an index now increases the insert operation cost/time and adds additional storage. If insert speed/volume is more important than reads keep the indexes away. Replicate and create an index on that copy.
    - sigstoat 5y ago
      > Adding an index now increases the insert operation cost/time that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert". the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.
- Philip-J-Fry 5y ago
dudeinjapan 5y ago
I agree. In the "new" FK-based approach, you'll still need to scan indexes to match the IP and email addresses (now in their own tables) to find the FKs, then do one more scan to match the FK values. I would think this would be significantly slower than a single compound index scan, assuming index scans are O(log(n)) Key thing is to use EXPLAIN and benchmark whatever you do. Then the right path will reveal itself...
deleted 5y ago
[deleted]
omegalulw 5y ago
> Sorry, no. The original schema was correct, and the new one is a mistake. Well, you also save a space by doing this (though presumably you only need to index the emails as IPs are already 128 bits). But other than that, I'm also not sure why the original schema was bad. If you were to build individual indexes on all four rows, you would essentially build the four id tables implicitly. You can calculate the intersection of hits on all four indexs that match your query to get your result. This is linear in the number of hits across all four indexes in the worst case but if you are careful about which index you look at first, you will probably be a lot more efficient, e.g. (from, ip, to, helo). Even with a multi index on the new schema, how do you search faster than this?

by ccleve 5y ago

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

> all major databases do these days Did everyone on HN miss that the database in question was whichever version of MySQL existed in 2002?

mst 5y ago
Which I was using in production at the time, and, yeah, what she ended up doing was a much better plan for that thing.
jeremyjh 5y ago
It actually doesn't matter. Even simple indexes would have made that schema work just fine, and MySQL could certainly index strings in 2002.

deleted 5y ago

[deleted]

silisili 5y ago

I think it depends a lot on the data. If those values like IP, From, To, etc keep repeating, you save a lot of space by normalizing it as she did. But strictly from a performance aspect, I agree it's a wash if both were done correctly.

philliphaydon 5y ago
Space is cheap now tho. Better to duplicate some data and avoid a bunch of joins than to worry about saving a few gb of space.
- kasey_junk 5y ago
  This system was written in 2002.
- acdha 5y ago
  It’s not that easy: you need to consider the total size and cardinality of the fields potentially being denormalized, too. If, say, the JOINed values fit in memory and, especially, if the raw value is much larger than the key it might be the case that you’re incurring a table scan to avoid something which stays in memory or allows the query to be satisfied from a modest sized index. I/O isn’t as cheap if you’re using a SAN or if you have many concurrent queries.
- wvenable 5y ago
  Using space to avoid joins will not necessarily improve performance in an RDBMS -- it might even make it worse.
  - philliphaydon 5y ago
    The inverse of your statement is also true. Denormalizing the database to avoid duplicating data will not necessarily improve performance in an RDBMS - it might even make it worse.

nerdponx 5y ago

And even if this is somehow not an ideal use of the database, it's certainly far from "terrible", and to conclude that the programmer who designed it was "clueless" is insulting to that programmer. Even if that programmer was you, years ago (as in the blog post). Moreover, unless you can prove with experimental data that the 3rd-normal-form version of the database performs significantly better or solves some other business problem, then I would argue that refactoring it is strictly worse. There are good reasons not to use email addresses as primary or foreign keys, but those reasons are conceptual ("business logic") and not technical.

late2part 5y ago

Deleted

emerongi 5y ago
To a degree, this is true though. An engineer's salary is a huge expense for a startup, it's straight up cheaper to spend more on cloud and have the engineer work on the product itself. Once you're bigger, you can optimize, of course.
- late2part 5y ago
  Deleted
  - emerongi 5y ago
    If this is a case from real life that you have seen, you should definitely elaborate more on it. Otherwise, you are creating an extreme example that has no significance in the discussion, as I could create similarly ridiculous examples for the other side. There are always costs and benefits to decisions. It seems that are you only looking at the costs and none of the benefits?
    - late2part 5y ago
      Deleted
bigbillheck 5y ago
> compute is cheap There was a whole thread yesterday about how a dude found out that it isn't: https://briananglin.me/posts/spending-5k-to-learn-how-database-indexes-work/ https://briananglin.me/posts/spending-5k-to-learn-how-databa... (also mentioned in the RbtB post)
- late2part 5y ago
  When do the rest of the folks figure it out?

maddynator 5y ago

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 are costly and will be costly if in tech industry, we continue to boot experienced engineers… the tacit knowledge those engineers have gained wi ll not be passed on and this means more people have to figure things out by themselves

adfgaertyqer 5y ago
You don't really need compression. Rows only need to persist for about an hour. The table can't be more than a few MiB. We can debate the Correct Implementation all day long. The fact of the matter is that adding any index to the original table, even the wrong index, would lead to a massive speedup. We can debate 2x or 5x speedups from compression or from choosing a different schema or a different index, but we get 10,000x from adding any index at all.
- ipaddr 5y ago
  Just to make this fun. Adding an index now increases the insert operation cost/time and adds additional storage. If insert speed/volume is more important than reads keep the indexes away. Replicate and create an index on that copy.
  - sigstoat 5y ago
    > Adding an index now increases the insert operation cost/time that insert is happening after you've checked the table to see if the record is present. so two operations whose times we care about are "select and accept email" and "select, tell the sender to come back in 20 minutes, and then insert". the insert time effectively doesn't matter, unless you've decided to abandon discussion of the original table entirely without mentioning it.
Philip-J-Fry 5y ago

dudeinjapan 5y ago

I agree. In the "new" FK-based approach, you'll still need to scan indexes to match the IP and email addresses (now in their own tables) to find the FKs, then do one more scan to match the FK values. I would think this would be significantly slower than a single compound index scan, assuming index scans are O(log(n)) Key thing is to use EXPLAIN and benchmark whatever you do. Then the right path will reveal itself...

deleted 5y ago

[deleted]

omegalulw 5y ago

> Sorry, no. The original schema was correct, and the new one is a mistake. Well, you also save a space by doing this (though presumably you only need to index the emails as IPs are already 128 bits). But other than that, I'm also not sure why the original schema was bad. If you were to build individual indexes on all four rows, you would essentially build the four id tables implicitly. You can calculate the intersection of hits on all four indexs that match your query to get your result. This is linear in the number of hits across all four indexes in the worst case but if you are careful about which index you look at first, you will probably be a lot more efficient, e.g. (from, ip, to, helo). Even with a multi index on the new schema, how do you search faster than this?