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

>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

Are you implying that isn't the case today? Thousands of (big) companies are still like that and will continue to be like that.

I write my own SQL, design tables and stuff, submit it for a review by someone 10x more qualified than myself, and at the end of the day I'll get a message back from a DBA saying "do this, it's better".

If you have one, more power to you. I'm currently making a good living as a freelance DBA/SRE for startups. With a team of 3-5 devs, some of which frontend and/or mobile devs, proper database knowledge is definitely thin on the ground in some places.

The question of it being in the cloud or not is highly orthogonal to schema design.

In 2002, you started seeing major increases in query run time with as little as 5 joins. Once you hit six you had to start thinking about rearchitecting your data or living with slow response times.

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.

> 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.

To my understanding, for example Firebird/Interbase had automatic key prefix compression as far back as early 2000s at the very least. I don't believe you could even turn it off.

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.

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.

> 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.

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...

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.

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?

It's not an index on every column. It's a single index across all columns.

Which will be updated every time there is a new combination.

Which is OK. It's not like the whole index has to be rebuilt. It's cheaper than O(log N).

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.

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.

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.

This system was written in 2002.

Using space to avoid joins will not necessarily improve performance in an RDBMS -- it might even make it worse.

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.

> all major databases do these days

Did everyone on HN miss that the database in question was whichever version of MySQL existed in 2002?

Which I was using in production at the time, and, yeah, what she ended up doing was a much better plan for that thing.

It actually doesn't matter. Even simple indexes would have made that schema work just fine, and MySQL could certainly index strings in 2002.

> 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?

Deleted

> 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-databa... (also mentioned in the RbtB post)

Depends on the provider. I've checked the pricing of 4 top relational database cloud vendors (Google, Amazon, Azure, IBM) and they all charge for the number of CPU cores and RAM you buy, not by the number of rows scanned like PlanetScale did in the post you refer to. They'll be more expensive than buying your own server but not nearly what PlanetScale charges for full table scan queries.

When do the rest of the folks figure it out?

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.

Deleted

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?

Deleted

If you're suggesting that we should double the size of the hardware rather than add a single index, then I respectfully disagree.

But your point is well-taken. Hardware is cheap.